topic Re: Help with sas macro in SAS Procedures

Help with sas macro

xliu1 — Fri, 25 Sep 2020 21:45:02 GMT

I try to create two macro variables to use throughout my programs. So the first gave me a value of 201505, and the second one gave me a list 15 terms up until 20201. These are what I want. However, when I execute the macro variables in later program. It only gave me one term information, which is the 201505 information. It might be because the TERMID is used twice in the macros and sas only recognizes the one used in first marco. What I really want to include in later program is the list of 15 terms generated by the second macro. Any ideas on how to modify the macros? Thanks!

/*The first one calculates the term which is 5 years before*/
%GLOBAL  Term_From ;
PROC SQL; 	
SELECT (input(termid, best6.) -503)
	INTO	:Term_From
	FROM  SID.SID_Termid where Flagcurrentterm='Y';
quit;

/*  The second one selects a list of 15  terms that could be included in later programs*/
%GLOBAL TERMCODE;
PROC SQL;
SELECT TERMID 
INTO :TERMCODE 
FROM SID.SID_Termid WHERE TERMID IN (select a.termid from SID.SID_Termid a where a.latestbenchmark = 'E' AND a.termid ge Put((&Term_From), 6.));
QUIT;

/* the following programs will execute macro variables*/

Proc sql;
	Create Table Crs_records AS
			SELECT count(*) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'
	        AND termid in ("&TERMCODE");

	Create table crs_rec_by_terms_bench AS
			SELECT  termid, bench, count(distinct UID) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'		
		    AND termid in ("&TERMCODE")
			GROUP BY termid, bench
			ORDER BY termid Desc, bench;
quit;

Re: Help with sas macro

Reeza — Fri, 25 Sep 2020 21:54:14 GMT

The query for the 15 list isn't correct. Add separated by "," to store them into a list, separated by a delimiter otherwise it only stores the last value.

PROC SQL; 	
SELECT (input(termid, best6.) -503)
	INTO	:Term_From separated by ", "
	FROM  SID.SID_Termid where Flagcurrentterm='Y';
quit;

You could avoid the use of macro variables with a subquery though

	Create table crs_rec_by_terms_bench AS
			SELECT  termid, bench, count(distinct UID) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'		
		    AND termid in (SELECT (input(termid, best6.) -503)
	FROM  SID.SID_Termid where Flagcurrentterm='Y')
			GROUP BY termid, bench
			ORDER BY termid Desc, bench;

@xliu1 wrote:

I try to create two macro variables to use throughout my programs. So the first gave me a value of 201505, and the second one gave me a list 15 terms up until 20201. These are what I want. However, when I execute the macro variables in later program. It only gave me one term information, which is the 201505 information. It might be because the TERMID is used twice in the macros and sas only recognizes the one used in first marco. What I really want to include in later program is the list of 15 terms generated by the second macro. Any ideas on how to modify the macros? Thanks!
/*The first one calculates the term which is 5 years before*/
%GLOBAL  Term_From ;
PROC SQL; 	
SELECT (input(termid, best6.) -503)
	INTO	:Term_From
	FROM  SID.SID_Termid where Flagcurrentterm='Y';
quit;

/*  The second one selects a list of 15  terms that could be included in later programs*/
%GLOBAL TERMCODE;
PROC SQL;
SELECT TERMID 
INTO :TERMCODE 
FROM SID.SID_Termid WHERE TERMID IN (select a.termid from SID.SID_Termid a where a.latestbenchmark = 'E' AND a.termid ge Put((&Term_From), 6.));
QUIT;
/* the following programs will execute macro variables*/
Proc sql;
	Create Table Crs_records AS
			SELECT count(*) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'
	        AND termid in ("&TERMCODE");

	Create table crs_rec_by_terms_bench AS
			SELECT  termid, bench, count(distinct UID) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'		
		    AND termid in ("&TERMCODE")
			GROUP BY termid, bench
			ORDER BY termid Desc, bench;
quit;

Re: Help with sas macro

xliu1 — Fri, 25 Sep 2020 22:11:01 GMT

Thanks for your prompt reply. I had tried subquery but it takes very long for sas to run because I need to pull over 9 million record. I followed your solution and added separated by "," in the macro, and run the following programs, but I have no observations in the table. Any idea what is wrong? thanks

Here is sas log:

533 %GLOBAL TERMCODE;
534 PROC SQL;
535 SELECT TERMID
536 INTO :TERMCODE separated by ","
537 FROM SID.SID_Termid WHERE TERMID IN (select a.termid from SID.SID_Termid a where
537! a.latestbenchmark = 'E' AND a.termid ge Put((&Term_From), 6.));
538 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.62 seconds
cpu time 0.01 seconds

539 Proc sql;
540 Create Table Crs_records AS
541 SELECT count(*) AS Num_rec
542 FROM SID.SID_COURSES_TAKEN CRS
543 WHERE fee_class_kind ^= 'P'
544 AND termid in ("&TERMCODE");
NOTE: Table WORK.CRS_RECORDS created, with 1 rows and 1 columns.

545
546 Create table crs_rec_by_terms_bench AS
547 SELECT termid, bench, count(distinct UID) AS Num_rec
548 FROM SID.SID_COURSES_TAKEN CRS
549 WHERE fee_class_kind ^= 'P'
550 AND termid in ("&TERMCODE")
551 GROUP BY termid, bench
552 ORDER BY termid Desc, bench;
NOTE: Table WORK.CRS_REC_BY_TERMS_BENCH created, with 0 rows and 3 columns.

Re: Help with sas macro

Tom — Fri, 25 Sep 2020 22:57:16 GMT

Are your variables numeric or character. You code seems confused about that.

If they are characters that consist only of digits how are the value formatted? Do they have leading zeros? Leading spaces?

You appear to be trying to convert variable the TERMID from SID.SID_Termid from character to number (although why you are trying to use the name of the format BEST as an INFORMAT it is not clear). And then you use the resulting macro variable as a character value to compare TERMID in SID.SID_COURSES_TAKEN by adding quotes around it. But you didn't tell SAS how to format the new number you made from the previous string. So you will probably get an 8 or 12 character string with the digits right aligned.

If the values are 6 digits with leading zeroes perhaps you want to use something like:

select distinct quote(put(input(termid, 32.) -503,Z6.))
  into :Term_From separated by ' '
  from  SID.SID_Termid where Flagcurrentterm='Y'
;
...
where TERMID in (&term_from)
...

If they instead do not have leading zeros then perhaps you want to left align the value generated by PUT before adding the quotes.

quote(strip(put(input(termid, 32.) -503,6.)))

Re: Help with sas macro

xliu1 — Fri, 25 Sep 2020 23:18:22 GMT

@Tom thanks so much! I tried your solution and it did work out in the programs. The column TERMID is a character variable. I use (input(termid, best6.) -503) to calculate the reference term that I want, which is 201505 (202008-503=201505). I put your quote statement in the second macro, and it gave me a list of terms with double quotes like "201505" ''201508"... then I execute the marco in later programs and I have 15 term information.

%GLOBAL TERMCODE;
PROC SQL;
SELECT quote(strip(put(input(termid, 32.),6.)))
INTO :TERMCODE separated by ", "
FROM SID.SID_Termid WHERE TERMID IN (select a.termid from SID.SID_Termid a where a.latestbenchmark = 'E' AND a.termid ge Put((&Term_From), 6.));
QUIT;

this is how i execute the macro

Proc sql;
	Create Table Crs_records AS
			SELECT count(*) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'
	        AND termid in (&TERMCODE);

	Create table crs_rec_by_terms_bench AS
			SELECT  termid, bench, count(distinct UID) AS Num_rec
			FROM SID.SID_COURSES_TAKEN CRS  
			WHERE  fee_class_kind ^= 'P'		
		    AND termid in (&TERMCODE)
			GROUP BY termid, bench
			ORDER BY termid Desc, bench;
quit;

Re: Help with sas macro

Reeza — Sat, 26 Sep 2020 17:51:53 GMT

That's a dangerous way to do that operation, I would highly recommend using appropriate SAS date functions instead.

I use (input(termid, best6.) -503) to calculate the reference term that I want, which is 201505 (202008-503=201505).

Re: Help with sas macro

xliu1 — Sat, 26 Sep 2020 18:14:29 GMT

Thanks @Reeza for your reply. I am new to SAS programming. Can you elaborate why it is dangerous to do that operation in sas? Thanks!

Re: Help with sas macro

Reeza — Mon, 28 Sep 2020 01:36:45 GMT

Because it doesn't always work and if your data is inaccurate due to typos or some other issue it will still work but you won't realize that it's wrong. ie if your date is 202101and you subtract 503 you get 201598. What does that mean? Is that what you expect? Do you need this to work for more than a few months?

Using date calculations that will property flip at the year end is less likely to run into issues over time.

Look up the INTNX() and INTCK() formulas.

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@xliu1 wrote:

Thanks @Reeza for your reply. I am new to SAS programming. Can you elaborate why it is dangerous to do that operation in sas? Thanks!

Re: Help with sas macro

xliu1 — Mon, 28 Sep 2020 12:26:38 GMT

Thanks!