DATA Step, Macro, Functions and more

Merge data sets

Accepted Solution Solved
Reply
Contributor sks
Contributor
Posts: 42
Accepted Solution

Merge data sets

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


Accepted Solutions
Solution
‎11-04-2011 01:40 AM
Super User
Posts: 10,035

Merge data sets

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

View solution in original post


All Replies
PROC Star
Posts: 7,480

Merge data sets

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;

Contributor sks
Contributor
Posts: 42

Merge data sets

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

PROC Star
Posts: 7,480

Merge data sets

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.

Solution
‎11-04-2011 01:40 AM
Super User
Posts: 10,035

Merge data sets

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

Contributor sks
Contributor
Posts: 42

Merge data sets

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 225 views
  • 3 likes
  • 3 in conversation