Help with sas macro

[xliu1]

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;

[Reeza]

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;

---

[xliu1]

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.

[Tom · Accepted solution]

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.)))

[xliu1]

@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;

[Reeza]

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).

[xliu1]

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!

[Reeza]

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/...

---

@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!

---

[xliu1]

Thanks!