Hello,
I have a SAS dataset which contains monthly customer information (variable examples below).
Customer Date Jan16 Feb16 Mar16 Apr16 .....................
For a SAS project I have to input the reporting date to be used to restrict the dataset (&mon. 01Jun2016 for example).
PROC SQL;
CREATE TABLE TEST AS
SELECT *
FROM HAVE
WHERE DATE <= "&mon."d;
QUIT;
I want to use this reporting date within the where clause of a step rather then inputting manually. For example for 01Jun2016 variable I want the following step using JUN2016:
PROC SQL;
CREATE TABLE WANT AS
SELECT *
FROM HAVE
WHERE JUN2016 = .;
QUIT;
How do I turn the inputted prompt character into a variable without the need for manual intervention? I hope I have explained it clearly!
First of all, this example illustrates the disadvantages of a wide vs. a long dataset structure. Putting data (the dates) into structure (variable names) only makes your tasks harder.
So the best solution is transposing your dataset into long format and then use the macro variable from the prompt in a where condition.
OTOH, you can easily use a macro variable as a variable name:
%let date=01jun2016;
%let mon=%substr(&date,3);
proc sql;
create table want as select *
from have
where &mon = .
;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.