topic Merge data sets in SAS Programming

Merge data sets

sks — Thu, 03 Nov 2011 17:38:56 GMT

Hi All,

I have a descritpion table, which contain 15 rows and have a default value of 0 for the 3 columns as shown below

desc_text column1 column2 column3

text1 0 0 0

text 2 0 0 0

: and so on

I then have a master table

type desc_text column1 column2 column3

type 1 text1 500 0 0

text2 0 300 0

type 2 text1 0 0 200

I am getting the master table from a different query, My issue is in the master table if there is no values associated for a particular type and description, I am not getting that row, but in my final table I need to be able to show all the description rows for a type whether or not it has a value. Something like

Final Table

type desc_text column1 column2 column3

type 1 text1 500 0 0

text2 0 300 0

text3 0 0 0

text4 0 0 0

type 2 text1 0 0 200

The description text is the same for all types and we have around 50 types, one way I could do this is in my description table have the descriptions for each type (approximately 50*15 rows) and then join on type from both the description and the master table.

I feel this is a very inefficient way of doing it, can anyone suggest a better way to merge the 2 data sets to get the final table in the format as shown above.

Any suggestion is greatly appreciated.

Thank You

Shri

Merge data sets

art297 — Thu, 03 Nov 2011 17:51:09 GMT

Why not just fix the file before attempting to process it. E.g.:

data need (drop=hold_type);

set have;

retain hold_type;

if not missing(type) then hold_type=type;

else type=hold_type;

run;

Merge data sets

sks — Thu, 03 Nov 2011 18:12:42 GMT

Art,

Thank You for your response, but I am a little confused on how to fix the file, In my master file I do have the type and some desc for each type, what I need if the for the type I have in my master table, I need to have all the descriptions. Can you explain your example a little more so I can understand how to "fix" the file.

Thank You

Shri

Merge data sets

art297 — Thu, 03 Nov 2011 18:28:36 GMT

I and others would probably need to see a good example of your data to be able to provide a reasonable answer. my code was based on your previous example which looked like the results of proc report where redundant class varriable data was only written once, followed by missing cells.

All my code did was establish a holding variable which was only reset if type wasn't missing, was retained across rows and, for each row, type was set to the holding variable (i.e., hold_type).

If you have more than one such class variable in your data, you would just have to set up hold variables for each of them, assign values to them when the values weren't missing, and then assign the held values to the actual variables.

Merge data sets

Ksharp — Fri, 04 Nov 2011 05:40:23 GMT

Assuming your description table has not duplicated desc_text.

data tran;
input desc_text    $    column1 column2     column3 ;
datalines;
text1                        0            0             0
text2                      0              0            0
text3                      0              0            0
text4                      0              0            0
;
run;
data master;
input type  $    desc_text      $            column1        column2      column3 ;
datalines;
type1          text1                      500                 0                0
type1            text2                          0               300              0
type2          text1                         0                  0              200
;
run;

proc sql;
 create table temp as
  select *
   from (select distinct type from master),(select * from tran);
quit;
data want;
 merge temp master;
 by type desc_text;
run;

Ksharp

Merge data sets

sks — Fri, 04 Nov 2011 14:25:59 GMT

Art/Ksharp,

Thank you for your suggestions, this has been very helpful.

Ksharp,

I am going to implement your suggestion and will let you know if is sufficient for what we are trying to do.

Shri