Consider the following dataset (simplified dataset, actual one will have approx 90 monthly columns):
Account | Jan_2013 | Feb_2013 | Mar_2013 | Record Date |
---|---|---|---|---|
001 | A | B | C | 3/3/2013 |
002 | B | B | B | 2/28/2013 |
003 | C | B | B | 1/5/2013 |
004 | B | A | C | 1/9/2013 |
005 | B | C | A | 3/3/2013 |
I'd like to do a lookup calculation, using the date from column "Record Date" to choose which column I pull a value from. A record date in March pulls the value from the March column. So the result set I'm looking for would be:
Account | Jan_2013 | Feb_2013 | Mar_2013 | Record Date | Calculated_Column |
---|---|---|---|---|---|
001 | A | B | C | 3/3/2013 | C |
002 | B | B | B | 2/28/2013 | B |
003 | C | B | B | 1/5/2013 | C |
004 | B | A | C | 1/9/2013 | B |
005 | B | C | A | 3/3/2013 | A |
Thanks
Look at the vvaluex, year and monname functions.
data have;
informat account $3. jan_2013 feb_2013 mar_2013 $1. record_date anydtdte.;
format record_date date9.;
input Account $ Jan_2013 $ Feb_2013 $ Mar_2013 $ Record_Date ;
cards;
001 A B C 3/3/2013
002 B B B 2/28/2013
003 C B B 1/5/2013
004 B A C 1/9/2013
005 B C A 3/3/2013
;
run;
data want;
set have;
name=put(Record_Date, monname3.)||"_"||put(year(Record_Date), 4.);
want=vvaluex(name);
run;
Look at the vvaluex, year and monname functions.
data have;
informat account $3. jan_2013 feb_2013 mar_2013 $1. record_date anydtdte.;
format record_date date9.;
input Account $ Jan_2013 $ Feb_2013 $ Mar_2013 $ Record_Date ;
cards;
001 A B C 3/3/2013
002 B B B 2/28/2013
003 C B B 1/5/2013
004 B A C 1/9/2013
005 B C A 3/3/2013
;
run;
data want;
set have;
name=put(Record_Date, monname3.)||"_"||put(year(Record_Date), 4.);
want=vvaluex(name);
run;
just another approach
This is only a problem because of the organisation of the dataset (aka self imposed shoot-in-foot mistake)
Escape from the problem by rewriting the data into one table of keys and values (often proc transpose is proposed for this). If the current information structure is more important than the problem, then create a view that dynamically transposes your data into keys and non-null values. For that structure the query is very straightforward
WHERE intnx( 'month', record_date, 0) = month_date
Assuming you would create in the transposed data, a key column called month_date storing the column name as a standard SAS date.
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.