BookmarkSubscribeRSS Feed
Riteshdell
Quartz | Level 8

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.

9 REPLIES 9
art297
Opal | Level 21

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

 

Riteshdell
Quartz | Level 8

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.

Reeza
Super User

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?

Riteshdell
Quartz | Level 8

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.

Reeza
Super User

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. 

 

 

SASKiwi
PROC Star

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.

novinosrin
Tourmaline | Level 20

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

Kurt_Bremser
Super User

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  
Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1277 views
  • 2 likes
  • 7 in conversation