Help using Base SAS procedures

Copy of variables with a prefix

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Copy of variables with a prefix

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


Accepted Solutions
Solution
‎09-23-2014 03:59 PM
Respected Advisor
Posts: 4,646

Re: Copy of variables with a prefix

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


All Replies
Super User
Posts: 10,500

Re: Copy of variables with a prefix

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.

Respected Advisor
Posts: 4,646

Re: Copy of variables with a prefix

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
Occasional Contributor
Posts: 5

Re: Copy of variables with a prefix

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

Super User
Super User
Posts: 7,401

Re: Copy of variables with a prefix

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;

Respected Advisor
Posts: 4,646

Re: Copy of variables with a prefix

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

PG
Occasional Contributor
Posts: 5

Re: Copy of variables with a prefix

Yes,  trans_id is unique in both the tables.

Occasional Contributor
Posts: 5

Re: Copy of variables with a prefix

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.

Super User
Super User
Posts: 7,401

Re: Copy of variables with a prefix

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.

Solution
‎09-23-2014 03:59 PM
Respected Advisor
Posts: 4,646

Re: Copy of variables with a prefix

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
Occasional Contributor
Posts: 5

Re: Copy of variables with a prefix

thanks PGstat and RW9.

thats works great!

Rgds, Danny

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 270 views
  • 3 likes
  • 4 in conversation