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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
art297
Opal | Level 21

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
Fluorite | Level 6 sks
Fluorite | Level 6

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
Opal | Level 21

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
Super User

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
Fluorite | Level 6 sks
Fluorite | Level 6

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 1073 views
  • 3 likes
  • 3 in conversation