Merge data sets

[sks]

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

[art297]

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;

[sks]

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

[art297]

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.

[Ksharp · Accepted solution]

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

[sks]

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