DATA Step, Macro, Functions and more

how to Read Master data code from Master table

Reply
Contributor
Posts: 63

how to Read Master data code from Master table

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;

 

s.JPG

 

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;

m.JPG


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 :

 

o.JPG

 

Thanks in advance.

PROC Star
Posts: 7,360

Re: how to Read Master data code from Master table

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

 

Contributor
Posts: 63

Re: how to Read Master data code from Master table

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.

Super User
Posts: 17,784

Re: how to Read Master data code from Master table

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?

Contributor
Posts: 63

Re: how to Read Master data code from Master table

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.

Super User
Posts: 17,784

Re: how to Read Master data code from Master table

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. 

 

 

Super User
Posts: 3,102

Re: how to Read Master data code from Master table

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.

PROC Star
Posts: 166

Re: how to Read Master data code from Master table

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

Super User
Posts: 6,928

Re: how to Read Master data code from Master table

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  
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,499

Re: how to Read Master data code from Master table

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;

Ask a Question
Discussion stats
  • 9 replies
  • 166 views
  • 2 likes
  • 7 in conversation