BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dannywilmot
Calcite | Level 5

Hi Experts,

I need to repeat some of the variables in my input table with prefix "MD_". These new varaibles should carry all the non-missing values from the original variable, however for missing values, it should join this table with master dataset on trans_id and get the value from there. List of required varaibles to be repeated are saved in a separate table (Repeat_Table). Can someone please help me understand how to build this.

01.jpgo

Many thanks in advance

DW

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

This works for me, from your Excel example file:

libname xl Excel "&sasforum.\datasets\DannyWilmot.xls" access=readonly mixed=yes;

proc sql noprint;

select cats("COALESCE(A.",var_repeat,", B.",var_repeat,") as MD_", var_repeat)

into :code separated by ", "

from xl.'Repeat_table$'n;

quit;

proc sql;

create table required_output as

select A.*, &code.

from xl.'Input_Table$'n as A left join

xl.'Master_Data$'n as B on A.trans = B.trans;

quit;

PG

PG

View solution in original post

10 REPLIES 10
ballardw
Super User

Since your example data doesn't show where the values 799 and 200 come from it is difficult to address what you want. Does your "repeat table" actually have any variable names? You show a column of what looks like variable names but that would not be a valid SAS dataset structure to contain different value, i.e. the 799 and 200 shown that appear to be associated with the System_ID. How do you link the values in Repeat to the input table rows?

the basic structure of the required output is easy:

data want;

     set have;

     MD_buy_date=buy_date;

     MD_System_ID=System_ID;

     MD_System_Name=System_name;

     MD_Rate=Rate;

run;

but how to update the missing values in want depends on the actual structure and content of your Repeat table.

PGStats
Opal | Level 21

I prefer to provide tested answers to hand waving explanations. Please post usable example data (not a screenshot) and include the structure of the master dataset. - PG

PG
dannywilmot
Calcite | Level 5

Hi PGStats,

I have now placed all the required inputs and expected  output in an excel file (attached). Unfortunately, I can not share the original data thats why I have tried to create a dummy dataset for this example, For this example we can assume that all the fields are character variables, Also orginal master dataset will have around 200 variables and Var_repeat will have around 30 variables which needs to be repeated in the input table.

Rgds, Danny

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like:

proc sql;
  create table WANT as
  select  A.TRANS, 
          A.RELEASE,
          A.QUALITY,
          A.MASK,
          A.BUY_DATE,
          A.SYSTEM_ID,
          ...
          COALESCE(A.SYSTEM_ID,MD.SYSTEM_ID) as MD_SYSTEM_ID, /* i.e. take a if present else from MD */

          MD.SYSTEM_NAME as MD_SYSTEM_NAME,
          MD.RATE as MD_RATE
  from    INPUT_DATA A
  left join MASTER_DATA MD
  on      A.TRANS=MD.TRANS
  and     A.BUY_DATE=MD.BY_DATE and A.SYSTEM_ID=MD.SYSTEM_ID;  /* Guessing a bit on the links */
quit;

PGStats
Opal | Level 21

Hi! Should we assume that Trans_ID is unique in InputTable and MasterData, or that the join should also involve another variable? - PG

PG
dannywilmot
Calcite | Level 5

Yes,  trans_id is unique in both the tables.

dannywilmot
Calcite | Level 5

Solution provided by RW9 will work but will require me to change the SQL everyone when the list of variables to repeat is changed,  can we get the list from the table instead of hard coding this in SQL.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Then you would need to look at putting the code in a macro, or the way I would do it is to generate the code:

data _null_;

     set dataset_with_repeat_variables end=last;

     if _n_=1 then call execute('proc sql;
                                               create table WANT as
                                               select  A.TRANS, 
                                                            A.RELEASE,
                                                            A.QUALITY,
                                                            A.MASK,
                                                            A.BUY_DATE,');

     call execute('A.'!!strip(name)!!',');

     if last then call execute('          ...
                                             COALESCE(A.SYSTEM_ID,MD.SYSTEM_ID) as MD_SYSTEM_ID, /* i.e. take a if present else from MD */

                                             MD.SYSTEM_NAME as MD_SYSTEM_NAME,
                                             MD.RATE as MD_RATE
                                               from    INPUT_DATA A
                                          left join MASTER_DATA MD
                                          on      A.TRANS=MD.TRANS
                                          and     A.BUY_DATE=MD.BY_DATE and A.SYSTEM_ID=MD.SYSTEM_ID;  /* Guessing a bit on the links */
                                        quit;');

run;

The above is not tested and I had to use ! instead of bar, but you get the idea.

PGStats
Opal | Level 21

This works for me, from your Excel example file:

libname xl Excel "&sasforum.\datasets\DannyWilmot.xls" access=readonly mixed=yes;

proc sql noprint;

select cats("COALESCE(A.",var_repeat,", B.",var_repeat,") as MD_", var_repeat)

into :code separated by ", "

from xl.'Repeat_table$'n;

quit;

proc sql;

create table required_output as

select A.*, &code.

from xl.'Input_Table$'n as A left join

xl.'Master_Data$'n as B on A.trans = B.trans;

quit;

PG

PG
dannywilmot
Calcite | Level 5

thanks PGstat and RW9.

thats works great!

Rgds, Danny

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1518 views
  • 3 likes
  • 4 in conversation