topic Re: how to Read Master data code from Master table in SAS Programming

how to Read Master data code from Master table

Riteshdell — Fri, 26 May 2017 03:56:20 GMT

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.

Re: how to Read Master data code from Master table

art297 — Fri, 26 May 2017 05:22:35 GMT

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

Re: how to Read Master data code from Master table

Riteshdell — Fri, 26 May 2017 05:55:53 GMT

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.

Re: how to Read Master data code from Master table

Reeza — Fri, 26 May 2017 06:02:45 GMT

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?

Re: how to Read Master data code from Master table

Riteshdell — Fri, 26 May 2017 06:06:12 GMT

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.

Re: how to Read Master data code from Master table

Reeza — Fri, 26 May 2017 06:10:37 GMT

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.

Re: how to Read Master data code from Master table

SASKiwi — Fri, 26 May 2017 07:20:33 GMT

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.

Re: how to Read Master data code from Master table

novinosrin — Fri, 26 May 2017 09:15:05 GMT

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

Re: how to Read Master data code from Master table

Kurt_Bremser — Fri, 26 May 2017 12:46:25 GMT

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

Re: how to Read Master data code from Master table

Tom — Fri, 26 May 2017 13:47:28 GMT

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;