cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
mhoward2
‎05-29-2024
  • 62 Posts
  • 0 Likes Given
  • 1 Solutions
  • 2 Likes Received

Re: How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-16-2024 04:21 PM
‎05-16-2024 04:21 PM
This is perfect! Thank you so much, I would not have known how to do this, your work is very much appreciated!!! ... View more

Re: How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-16-2024 09:58 AM
‎05-16-2024 09:58 AM
if there is a way to accomplish this without proc sql, im all ears! Thanks! ... View more

Re: How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-16-2024 09:01 AM
‎05-16-2024 09:01 AM
I agree the output data structure is not ideal, but I have been told it needs to be that way. I have posted an example of the source data above in my response to ballardw. Any and all help is appreciated! Thank you! ... View more

Re: How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-16-2024 08:59 AM
‎05-16-2024 08:59 AM
I agree the output data structure is not ideal, but I have been told it needs to be that way. I have posted an example of the source data above in my response to ballardw. Any and all help is appreciated! ... View more

Re: How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-16-2024 08:56 AM
‎05-16-2024 08:56 AM
Hi, thank you for responding, my source data looks like this below. I understand the output of each being its own column is a little weird, but I have been told that is how it must be for now.   ID_VARIABLE FIRST_YYYYMM SECOND_YYYYMM SUM_COLUMN 1 201905 201905 15 2 201901 201901 0 2 201901 201902 0 2 201902 201902 20 2 201904 201904 0 2 201907 201907 0 2 201910 201910 0 3 201901 201901 70 3 201908 201908 0 3 201910 201912 30 3 201911 201911 110   As for your other question, yes there will always be 2 "full year" periods where the FIRST_YYYYMM is between 01 and 12 and the SECOND_YYYYMM is 01 and 02 of the next year.   I am still looking to see how I would accomplish this easily with less lines of code in the select statement. Any and all help would be appreciated, thank you! ... View more

How to use macro variable inside a Select Statement

by Obsidian | Level 7 in SAS Programming
‎05-07-2024 02:56 PM
‎05-07-2024 02:56 PM
Hi, I have some code that sums up monthly values and assigns each month as a new column. The problem is the code is ugly and I feel like there should be a better way to do this. Here is the code:   PROC SQL; CREATE TABLE TMP1DAY.MONTHLY_SUMMARY AS SELECT t1.ID_VARIABLE, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201901" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201902", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201901, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201902" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201903", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201902, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201903" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201904", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201903, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201904" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201905", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201904, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201905" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201906", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201905, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201906" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201907", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201906, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201907" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201908", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201907, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201908" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201909", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201908, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201909" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201910", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201909, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201910" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201911", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201910, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201911" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "201912", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201911, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201912" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "202001", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201912_01, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "201901" AND "201912" AND t1.SECOND_YYYYMM BETWEEN "201901" AND "202002", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_201912_02, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202001" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202002", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202001, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202002" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202003", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202002, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202003" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202004", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202003, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202004" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202005", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202004, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202005" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202006", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202005, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202006" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202007", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202006, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202007" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202008", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202007, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202008" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202009", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202008, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202009" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202010", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202009, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202010" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202011", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202010, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202011" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202012", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202011, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202012" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202101", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202012_01, SUM(IFN(t1.FIRST_YYYYMM BETWEEN "202001" AND "202012" AND t1.SECOND_YYYYMM BETWEEN "202001" AND "202102", t1.SUM_COLUMN, 0)) AS SUM_COLUMN_202012_02 FROM WORK.PRE_MONTHLY_SUMMARY t1 GROUP BY 1; QUIT; How would I instead write this to do the same thing but programmatically/with macro variables? its already out of hand and I will need to add more years which will just make it even worse. Any and all advice is appreciated.   Thanks in advance! ... View more

Autoexec process flow check values with user before running

by Obsidian | Level 7 in SAS Programming
‎10-31-2023 10:32 AM
‎10-31-2023 10:32 AM
Is it possible to have an autoexec process flow on SAS Enterprise Guide that runs a bit of a code, then ask the user if the output is accurate before continuing?   For example, I have a process flow that uses SAS functions to get dates depening on the day the code is being run. So SAS knows today is 10/31/2023, therefore it automatically creates a variable that assigns a variable like Date_Start_Month = 202310.   The question is, can I somehow hit run autoexec, run the code that assigns Date_Start_Month variable, then have a prompt appear that says "Date_Start_Month = 202310. Is this correct?" and then have an option for the user to either hit "Yes" and it will run the rest of the process flow, OR hit "No" and have them be able to change the Date_Start_Month variable themselves.   Is this possible? Thanks! ... View more

Duplicate Rows But Change One Variable

by Obsidian | Level 7 in SAS Programming
‎08-23-2023 09:24 AM
‎08-23-2023 09:24 AM
I have a table that looks like the following:   ID1 ID2 Type Value1 A C Red 3 A C Blue 2 A D Green 2 A D Purple 1 B C Orange 3 B C Yellow 2 B D Brown 1   And want to duplicate rows if ID2 = 'D', and then change ID2 to 'C'.   Meaning I want a new table that looks like:   ID1 ID2 Type Value1 A C Red 3 A C Blue 2 A D Green 2 A D Purple 1 B C Orange 3 B C Yellow 2 B D Brown 1 A C Green 2 A C Purple 1 B C Brown 1   You can see the last three rows in that table are the D rows in the first table, relabeled as C, but also kept the original D rows.   Any ideas? Thanks in advance! ... View more

Re: Top Observations Of Each Grouping

by Obsidian | Level 7 in SAS Programming
‎08-07-2023 02:32 PM
‎08-07-2023 02:32 PM
Thank you! Is there no way to do it within the same SQL Step? ... View more

Top Observations Of Each Grouping

by Obsidian | Level 7 in SAS Programming
‎08-07-2023 02:19 PM
‎08-07-2023 02:19 PM
I tried a few solutions online and kept getting errors. How do output the top 10 observations by grouping? Current code:   PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.BUCKET1, t1.BUCKET2, t1.CODE, SUM(CASE WHEN t1.CRITERIA1='A' THEN t1.SUM_OF_CNT END) AS A_CNT FROM WORK.HAVE t1 WHERE t1.CRITERIA2 = 'B' GROUP BY 1,2,3 ORDER BY t1.BUCKET1, t1.BUCKET2, CALCULATED A_CNT DESC; QUIT; There can be any number of combinations of BUCKET1 and BUCKET2. With this code, each combination gets as many rows as there are CODES within that combination. How do I only output the top 10 codes for each combination?   Thanks in advance! ... View more

Count Distinct nested within Sum Function based on Case When Statement

by Obsidian | Level 7 in SAS Programming
‎07-10-2023 09:46 AM
‎07-10-2023 09:46 AM
Hi,   I coded the following thinking it would work but unfortunately SAS apparently cannot do what I am trying to do. Is there another way to accomplish this table?   PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.BUCKET, t1.GROUP, t1.CODE, t1.CODE_DESC, SUM(CASE WHEN t1.INDICATOR='IN' THEN COUNT(DISTINCT(t1.UNIQ_ID)) ELSE 0 END) AS IN_UNIQ_ID, SUM(CASE WHEN t1.INDICATOR='IN' THEN t1.SUM_OF_DOLLARS ELSE 0 END) AS IN_DOLLARS, SUM(CASE WHEN t1.INDICATOR='IN' THEN t1.SUM_OF_FIELD_COUNT ELSE 0 END) AS IN_FIELD_COUNT, SUM(CASE WHEN t1.INDICATOR='IN' THEN (CALCULATED IN_DOLLARS/CALCULATED IN_FIELD_COUNT) ELSE 0 END) AS IN_AVG_DOLLARS, SUM(CASE WHEN t1.INDICATOR='OUT' THEN COUNT(DISTINCT(t1.UNIQ_ID))ELSE 0 END) AS OUT_UNIQ_ID, SUM(CASE WHEN t1.INDICATOR='OUT' THEN t1.SUM_OF_DOLLARS ELSE 0 END) AS OUT_DOLLARS, SUM(CASE WHEN t1.INDICATOR='OUT' THEN t1.SUM_OF_FIELD_COUNT ELSE 0 END) AS OUT_FIELD_COUNT, SUM(CASE WHEN t1.INDICATOR='OUT' THEN (CALCULATED OUT_DOLLARS/CALCULATED OUT_FIELD_COUNT) ELSE 0 END) AS OUT_AVG_DOLLARS, FROM WORK.HAVE t1 GROUP BY 1,2,3,4 ORDER BY t1.BUCKET, t1.GROUP, CALCULATED IN_FIELD_COUNT DESC; QUIT; I get the following errors: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results. ERROR: Summary functions nested in this way are not supported. Any help here is much appreciated! Thank you in advance! ... View more

Case When statement for an Inner Join

by Obsidian | Level 7 in SAS Programming
‎02-07-2023 02:13 PM
‎02-07-2023 02:13 PM
I have a join on a couple tables that takes FOREVER to run. Way way longer than it should and one of the notes I haven't noticed before states:    NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. I'm not sure what that means exactly but a quick google indicates that it probably isn't what I'm trying to accomplish.   PROC SQL; CREATE TABLE WORK.TABLE3 AS SELECT t1.*, t2.NAME, t2.DATE FROM WORK.TABLE1 t1 INNER JOIN WORK.TABLE2 t2 ON (CASE WHEN t1.CAT1 NOT IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2= t2.ID2 AND t2.ID3 = t1.ID3) WHEN t1.CAT1 IN ('CATA', 'CATB') THEN (t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2) END); QUIT; Is this an acceptable way to join? Is there a better way?   Thanks in advance. ... View more

Re: Let Statement Inside Macro Not Assigning Value

by Obsidian | Level 7 in SAS Programming
‎01-27-2023 12:50 PM
‎01-27-2023 12:50 PM
Just fade me fam. Thank you so much!! I was fully aboard the struggle bus. ... View more

Let Statement Inside Macro Not Assigning Value

by Obsidian | Level 7 in SAS Programming
‎01-27-2023 12:44 PM
‎01-27-2023 12:44 PM
Hi all, I am attempting to assign a macro variable that is essentially a string of SQL code, but SAS is not assigning it for some reason. Here is my code:     %MACRO DETERMINE_SOURCE_TABLE(); %IF &Inc_End. = &End_YYYY.0331 %THEN %DO; PROC SQL; CREATE TABLE WORK.Table2 AS SELECT DISTINCT t1.CATEGORY, t1.PREFERENCE MIN(t1.EFF_DATE) AS MIN_OF_DATE FROM WORK.Table1 T1 WHERE t1.DRUG_CATEGORY ~= '<NA>' GROUP BY 1,2,3,4,5,6,7,8,9,10,11; QUIT; /*GET RID OF LAST 3 MONTHS OF CLAIMS FROM PREVIOUS YEAR*/ PROC SQL; CREATE TABLE WORK.Table3 AS SELECT DISTINCT t1.* FROM WORK.Table1 T1 WHERE t1.EFF_DATE_SK GE &Inc_End_YYYY.0101; QUIT; %LET Source = t2.MIN_OF_DATE AS MIN_OF_DATE FROM WORK.Table3 t1 LEFT JOIN WORK.Table2 t2 ON t1.CATEGORY = t2.CATEGORY AND t1.PREFERENCE = t2.PREFERENCE WHERE t1.CATEGORY NE '<NA>' GROUP BY 1,2; %END; %IF &Inc_End. > &End_YYYY.0331 %THEN %DO; %LET Source = MIN(t1.EFF_DATE_SK) AS MIN_OF_DATE FROM WORK.Table1 t1 WHERE t1.CATEGORY NE '<NA>' GROUP BY 1,2; %END; %MEND DETERMINE_SOURCE_TABLE; %DETERMINE_SOURCE_TABLE %PUT &Source; As you can see, when my first if Statement is true, I do some code, but when my second if Statement is true, I just set the variable. For the code im running the first if statement is true but the variable is not setting correctly. Here is from the log MLOGIC(DETERMINE_SOURCE_TABLE): Beginning execution. 88 %PUT &Source; SYMBOLGEN: Macro variable INC_END resolves to 20221231 SYMBOLGEN: Macro variable END_YYYY resolves to 2022 MLOGIC(DETERMINE_SOURCE_TABLE): %IF condition &Inc_End. = &End_YYYY.0331 is FALSE SYMBOLGEN: Macro variable INC_END resolves to 20221231 SYMBOLGEN: Macro variable END_YYYY resolves to 2022 MLOGIC(DETERMINE_SOURCE_TABLE): %IF condition &Inc_End. > &End_YYYY.0331 is TRUE MLOGIC(DETERMINE_SOURCE_TABLE): %LET (variable name is SOURCE) MLOGIC(DETERMINE_SOURCE_TABLE): Ending execution. WARNING: Apparent symbolic reference SOURCE not resolved. &Source Any thoughts? Thanks!   ... View more

Re: How To Use If Statement in a Join

by Obsidian | Level 7 in SAS Programming
‎02-09-2022 01:04 PM
‎02-09-2022 01:04 PM
Those two pieces solved it, thank you so much! Clearly I need to learn how to use functions in SAS better! ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-29-2024 06:12 PM
Likes from
View All
Quick links