Solved
Contributor
Posts: 27

Lookup column values

Consider the following dataset (simplified dataset, actual one will have approx 90 monthly columns):

AccountJan_2013Feb_2013Mar_2013Record Date

001

ABC3/3/2013
002BBB

2/28/2013

003CBB1/5/2013
004BAC1/9/2013
005BCA3/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:

AccountJan_2013Feb_2013Mar_2013Record DateCalculated_Column

001

ABC3/3/2013C
002BBB

2/28/2013

B
003CBB1/5/2013C
004BAC1/9/2013B
005BCA3/3/2013A

Thanks

Accepted Solutions
Solution
‎12-04-2013 06:03 PM
Super User
Posts: 23,700

Re: Lookup column values

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;

All Replies
Solution
‎12-04-2013 06:03 PM
Super User
Posts: 23,700

Re: Lookup column values

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;

Valued Guide
Posts: 2,191

Re: Lookup column values

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.

🔒 This topic is solved and locked.