I am using macro function to select certain terms to use in the following programs, and use "%put" function to check whether I have selected correct terms. All I want to select is 201205 (i.e., 2nd row under TERM column) through 202101 (i.e., 28th row under TERM column). However, the output shows that terms selected are 201201-202008. How should I change the program to make correct selection? Here I attached a screen shot of the table I used. Thanks for any feedback!
PROC SQL ; *noprint; SELECT TERM INTO :Term2 - :Term28 FROM TERM_REF; %put &Term2-&Term28; QUIT;
You can use _n_ to track the row numbers first. And then use proc sql.
data have; do term=201201 to 201230; output; end; run; data keep1; set have; row=_n_; run; proc sql noprint; select term into: term2 from keep1 where row=2; select term into: term28 from keep1 where row=28; quit; %put &term2; %put &term28;
If you have more rows then you can automate it using macros. But assuming you need only 2 rows, this is the simplest way.
Depends on WHY you want to start with the second observation.
Do you always want to skip the first observation? Then just exclude it from the input to your SELECT statement.
Or do you want to limit by the values you mentioned instead? Are those dates with format that only displays 6 digits? Or are the numbers like 201,201?
where term_ref >= 201205
Incidentally, the code could be made more flexible like this:
%LET Start = 201205; %LET Stop = 202101; PROC SQL ; *noprint; SELECT COUNT(TERM) AS Terms_Retreived INTO :Terms_Retrieved TRIMMED FROM TERM_REF WHERE &Start <= Term <= &Stop ; SELECT TERM INTO :Term1 - FROM TERM_REF WHERE &Start <= Term <= &Stop ; QUIT; %IF %BQUOTE(&Terms_Retrieved) %THEN %DO; %put NOTE: &Term1-&&Term&Terms_Retrieved; %END;
With the above code, you don't need to know in advance how many terms (or which terms) will be retrieved. SAS will figure out how many terms are in the range you specify via the %LET statements, create the appropriate number of macro variables, and display the range in the log for you.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.