topic Re: Lookup column values in SAS Programming

Lookup column values

rwnj — Wed, 04 Dec 2013 22:39:43 GMT

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

Re: Lookup column values

Reeza — Wed, 04 Dec 2013 23:03:12 GMT

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;

Re: Lookup column values

Peter_C — Thu, 05 Dec 2013 10:50:07 GMT

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.