Hello Expert,
I am stuck in a logic to read master data value from Master table where all codes and description value are available.
I have a Source Data Set "HAVE" having 4 columns value (dynamic column name )
ID is a primary key and rest all columns are dimesion column for it, it store all codes for it, but I need to read the code and lookup in to Master table which store all information about text and M_cd.
Source table :- have :
data have;
input Id $ gender_cd $ martial_status $ currency_cd $ ;
datalines;
1 M 0 10
2 V 1 20
3 G 2 30
4 M 1 20
;
run;
Master table : Master
data master;
length column $14.;
input column $ text $ m_cd $;
datalines;
Gender_cd M 1
Gender_cd V 2
Gender_cd G 4
Martial_status 0 bachlor
Martial_status 1 single
martial_status 2 married
currency_cd 10 USD
Currency_CD 20 INR
Currecny_CD 30 AUD
;
run;
Explaination : In Source table Have, I want to see my data like Gender_CD , Martial_Status, Currency_CD, should be check from Master table and respective value should be populate.
Example- Suppose I have a source table HAVE, COlumn name (Gender_CD) , value "M" it should check the value in master table.
Now We will check in Master table, So first it should check Gender_CD value in master table and then it should jump in "M" and then it should read its respective Master data value (M_CD) (1).
Same thing it should do, First check the rest all the vlaue and Column name = Column value from master then it should jump on Text column and then it should pick "M_CD" value.
Output Below I am expecting :
Thanks in advance.
What you want can be easily done by creating formats from the master file. e.g.:
data for4mat; set master (rename=(column=fmtname text=start m_cd=label)); fmtname=catt('$',lowcase(substr(fmtname,1,3))); type='C'; run; proc sort data=for4mat; by fmtname start; run; proc format cntlin = for4mat; run; data want; set have; format gender_cd $gen.; format martial_status $mar.; format currency_cd $cur.; run; proc print data=want; run;
Art, CEO, AnalystFinder.com
Thanks Art297, for the solution by proc format.
But Soure I have many column like some time more than 150 columns (dynamic).
How it will be fessible to write format for every one, My intention was to lookup in to master table and take refrence of data and take code value for it.
I will really appreciate if you can help me with this method.
1. You can create a format from a dataset. It is via code - one data step.
2. Use 150 left joins and you have to write those out manually
3. Load into a temporary array
4. Hash tables
5. Some sort of macro.
Still want option 2?
Hello @Reeza - taking 150 joins , it will be huge task.
IS there any way like can we transpose master table and then lookup to master_transpose table from source one.?
i dont know how much I correct ot not.,
Please help.
Did you run the solution above? It creates the formats for all you just need to apply them. It doesn't matter how many vars are there ...
It really is the most efficient way.
You could transpose but you'll have to make sure to separate character and numeric variables.
I maintain a system with > 150 lookup formats in it, all automatically generated from datasets. I'm literally saving hundreds of lines of code by doing so. I agree with @Reeza, formats are the best option.
OK, I trust you attempted all the well laid out guidelines of Reeza. Here is the Hash solution. Depending on the data types of variables in your HAVE dataset, you should be able to make slight adjustments because my testing is limited to using only your sample.
data have;
input Id $ gender_cd $ martial_status $ currency_cd $ ;
datalines;
1 M 0 10
2 V 1 20
3 G 2 30
4 M 1 20
;
run;
data master;
length column $14.;
input column $ text $ m_cd $;
datalines;
Gender_cd M 1
Gender_cd V 2
Gender_cd G 4
Martial_status 0 bachlor
Martial_status 1 single
martial_status 2 married
currency_cd 10 USD
Currency_CD 20 INR
Currecny_CD 30 AUD
;
run;
data want;
if _N_ = 1 then
do;
if 0 then do;
set have;
set master(keep=text m_cd);
end;
declare hash myhash(dataset:'master(keep=text m_cd)',multidata:'yes' );
rc = myhash.defineKey('text');
rc = myhash.defineData('m_cd');
rc = myhash.defineDone( );
end;
set have;
array list(*) gender_cd--currency_cd;
do _n_=1 to dim(list);
rc_temp=list(_n_);
if myhash.find(key:rc_temp)=0 then
list(_n_)=m_cd;
else call missing(m_cd);
end;
drop rc: m_cd text;
run;
Regards,
Naveen Srinivasan
The proc format code looks like this:
data have;
input Id $ gender_cd $ marital_status $ currency_cd $ ;
datalines;
1 M 0 10
2 V 1 20
3 G 2 30
4 M 1 20
;
run;
data master;
length column $14.;
input column $ text $ m_cd $;
datalines;
gender_cd M 1
gender_cd V 2
gender_cd G 4
marital_status 0 bachlor
marital_status 1 single
marital_status 2 married
currency_cd 10 USD
currency_cd 20 INR
currency_cd 30 AUD
;
run;
data cntlin;
set master (
rename=(
column=fmtname
text=start
m_cd=label
)
);
type = 'C';
run;
proc format library=work cntlin=cntlin;
run;
proc sort
data=master (keep=column)
out=columns
nodupkey
;
by column;
run;
data _null_;
call execute('data want; set have (rename=(');
do until (eof1);
set columns end=eof1;
call execute(trim(column)!!'=_'!!trim(column)!! ' ');
end;
call execute('));');
do until (eof2);
set columns end=eof2;
call execute(trim(column)!!'=put(_'!!trim(column)!!',$'!!trim(column)!!'.);');
call execute('drop _'!!trim(column)!!';');
end;
call execute('run;');
stop;
run;
proc print data=want noobs;
run;
Result:
currency_ gender_ marital_ Id cd cd status 1 USD 1 bachlor 2 INR 2 single 3 AUD 4 married 4 INR 1 single
Formats are the way to go here.
But if you did want to tyr to "join" the two then you should first transpose the HAVE data to a tall-skinny (name-value) format. Then you can join the two tables. You could then tranpose it back.
data have;
input Id $ gender_cd $ martial_status $ currency_cd $ ;
datalines;
1 M 0 10
2 V 1 20
3 G 2 30
4 M 1 20
;
data master;
input column :$14. text $ m_cd $;
datalines;
Gender_cd M 1
Gender_cd V 2
Gender_cd G 4
Martial_status 0 bachlor
Martial_status 1 single
martial_status 2 married
currency_cd 10 USD
Currency_CD 20 INR
Currency_CD 30 AUD
;
proc transpose data=have name=column out=tall1 (where=(upcase(column) ne 'ID')) ;
by id ;
var _all_ ;
run;
proc sql ;
create table tall2 as
select a.id,a.column,a.col1,b.m_cd
from tall1 a
left join master b
on upcase(a.column) = upcase(b.column) and strip(a.col1)=strip(b.text)
order by 1,2
;
quit;
proc transpose data=tall2 out=want(drop=_name_) ;
by id;
id column;
var m_cd ;
run;
proc print data=have; title 'have'; run;
proc print data=master; title 'master'; run;
proc print data=tall1; title 'tall1'; run;
proc print data=tall2; title 'tall2'; run;
proc print data=want; title 'want'; run;
title;
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.