I have a table where I’d like to select only the record with the maximum date: The issue is that I have some IDs in the table where the maximum date is in 2022, so that is what gets selected. How do I select the maximum date for a record that is before 01JAN2022 so that my output look like
Tag: sas
counting consecutive months in sas
From the sample data below, I’m trying to count how many consecutive months a member has. In the event if an ID has a gap month in the middle, the output should show the most recent count of consecutive months this member had, see example below. dataset=one ID Month 72 01SEP2020 72 01OCT2020 72 01NOV2020 72 01DEC2020 72 01FEB2021 72
COUNT to return NULL as 0 in same table in SAS PROC SQL with GROUP BY
I am having a task where I have 1 table. There is a unique ID for transactions, transaction dates, and store names. My task would be to count the number of transactions every store made during March. This, in itself, is simple: This would lead to a table where a store is present only if it had a transaction in
How to generate dynamic table having begin month date and end month date Teradata or SAS SQL
I’d like to generate a dynamic Table with the start date of a month as a column and the end date of the month as another column. Ideally, I’d like to provide two years, f.e. 2016 and 2021. The output I’d like to have when providing these two years is the following: Kindly note that I require the output for
SAS computing multiple new variables from one row
I have a dataset as listed below: and I want to calculate 3 new variables (ERR_CODE, ERR_DETAIL, ERR_ID) according to behavior of certain columns. If V1 is greater than 4 then ERR_CODE = A and ERR_DETAIL = “Out of range” and ERR_ID = [ID]_A If V2 is greater than 4 then ERR_CODE = B and ERR_DETAIL = “Check Log” and
Use a macro in SAS ‘to_date’ conversion
Being forced to pass my code in explicit pass though, the date conversion no longer works. I would like to use the &aaaa macro in the convert function. My code example : I got an error : “The (full) year must be between -4713 and +9999 and be different from 0”. Does anyone have a solution? Answer Macro variables only
How to delete some observations in some groups based on a condition
I would like delete some observations in some groups based on a condition. My condition is each group : if (imput=”toto”) then delete but if a whole group has imput=”toto” then …
How to query SSMS via SAS EG ODBC connection while using data from WORK?
Is there a way to send a SAS dataset through a proc sql odbc query in SAS EG so that it can be taken in and used by SQL server? ex) SAS Data WORK.A contains 3 columns ID, col1, col2. I have a table in Sql Management Studio environment with the same ID column. I would like to (somehow) do
How to obtain summary summation when GROUP BY is not working?
I have data with the columns User_id (char) filename (char) filesize (numeric) Every user_id has more than 5 files to it’s name, of different filesize values. Problem statement: I want to have a summary of this table, with columns, User_id, Filesize, where it shows the total size occupied by each user id. It tried Group By user id, in Proc
how to delete first occurrence of each group in sas table?
I have data as follows: ID,date 1,27-08-2020 1,28-08-2020 1,30-08-2020 2,05-08-2020 3,06-08-2020 3,10-08-2020 I have to delete first occurrence for each ID group. so that data becomes ID,date 1,28-08-…