Hi Guys
Need some help with this problem.
This is the two tables I have:
data sample;
infile datalines missover;
input Date $ Type1 Type2 $ Money P_10 P_108 P_9 O_100 O_98 O_5 P_50 P_3 O_355;
datalines;
20170101 0 A 100 50 10 . 40
20170101 0 A 110 . . . . 110
20170101 1 B 1200 200 . . . . 500 . . 500
20170101 1 B 202 100 . . . . . 102
20170101 5 C 5 . . . . 5
;
run;
data reference_id;
infile datalines missover;
input ID Descrip $ Status $ category $;
datalines;
10 P PAID GOOD
108 P PAID GOOD
9 P PAID BAD
100 M PAID GOOD
98 M PAID BAD
5 M PAID GOOD
50 P PAID BAD
3 P PAID GOOD
355 M PAID N/A
;
run;
I need to extract all the ID's from sample into an array (remove the prefix) and join it with the ID properties in the reference_id table.
The ID should be replaced with its respective properties e.g P_10 in sample table equals ID 10 in reference_id table - the output should read P_PAID_GOOD.
This is the output I desire.
data output;
infile datalines missover;
input Date $ Type1 Type2 $ Money P_PAID_GOOD P_PAID_BAD M_PAID_GOOD M_PAID_BAD M_PAID_N_A ;
datalines;
20170101 0 A 100 60 . 40
20170101 0 A 110 . . . 110
20170101 1 B 1200 200 . 500 . 500
20170101 1 B 202 100 . . 102 .
20170101 5 C 5 . . . 5 .
;
run;
... View more
