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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.