DATA Step, Macro, Functions and more

How do we combine 2 datasets of different structures

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

How do we combine 2 datasets of different structures

I have 2 datasets..

 

SAS Dataset 1:

 

ID                         REC_NO                  Org Name                         Country                 Date

1111                      1                              Pizza Hut                         India                     18052016

 

SAS Dataset 2: 

ID                        Sl_No                       User               Email                     First Name      Last Name       Outlet

2222                       1                           Gary              Gary@abc.com      Gary               Linekar             Gary's Pizza          
  

 

Output Dataset Expected:

 

1111                      1                              Pizza Hut                         India                     18052016

2222                       1                           Gary              Gary@abc.com      Gary               Linekar             Gary's Pizza

 


Is there any way to concatinate 2 SAS datasets of different structure ??? 

 

 

I tried normal data set statement but that is not the result expected.


Accepted Solutions
Solution
‎05-19-2016 07:20 AM
Contributor
Posts: 32

Re: How do we combine 2 datasets of different structures

Hi all, Thanks for your valuable inputs.. I know this was kind of a crazy question. But i had to generate a report which would result like this!..

 

How i did it is by exporting both the SAS datasets & then used Unix

 

cat dataset1 > dataset 2.. which did the trick for me!!!

View solution in original post


All Replies
Super User
Posts: 6,936

Re: How do we combine 2 datasets of different structures

What you want to achieve runs contrary to the concept of datasets, where a column has the same attributes (type, size, name, display format) throughout all observations/records.

In your case, you don't even have the same number of columns in the first place.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 32

Re: How do we combine 2 datasets of different structures

Ok so I have 2 SAS dataset & i have to generate a report out of these two dataset(combined) where dataset 1 as my 1st record followed by dataset 2.

Super User
Posts: 17,824

Re: How do we combine 2 datasets of different structures

Do you have multiple records per dataset that need to be reported. 

Please take the time to detail your question. We're guessing at solutions and what to ask you. 

Contributor
Posts: 32

Re: How do we combine 2 datasets of different structures

Dataset 1 would be my header record.(only 1 record with fixed columns) Dataset 2 would be my data record.( n records with fixed columns). 

Super User
Super User
Posts: 7,401

Re: How do we combine 2 datasets of different structures

I agree with @KurtBremser, what you want to do makes no sense.  The only thing I can thin of is that this is for a report?  If so then you can do something like:

proc sql;
  create table WANT as
  select  ID,
          REC_NO,
          ORG_NAME as COL1 length=200,
          COUNTRY as COL2 length=200,
          DATE as COL3 length=200,
          "" as COL4 length=200,
          "" as COL5 length=200
  from    HAVE1 
  union all
  select  ID,
          SI_NO as REC_NO,
          USER as COL1,
          EMAIL as COL2,
          FIRST_NAME as COL3,
          LAST_NAME as COL4,
          OUTLET as COL5
  from    HAVE2;
quit;
Super User
Posts: 17,824

Re: How do we combine 2 datasets of different structures

Do you want to stack the data or add fields from one to another to create a single row with more variables?

 

Given your sample the second seems more useful and would be obtained via a proc SQL join or data step merge. 

Super User
Posts: 5,082

Re: How do we combine 2 datasets of different structures

You can do it ... sort of ... with this program:

 

data want;

set dataset1 dataset2 (rename=(user=OrgName FirstName=Country LastName=Date));

by id;

run;

 

There are several issues you might encounter, however.  LASTNAME and DATE would both have to be character.  If that's not the case now,  you would have to change DATASET1 ahead of time to make it the case.  And variables might have different lengths, requiring a LENGTH statement to avoid truncation.  For example, if ORGNAME has a shorter length than USER, you would need to add this statement before the SET statement:

 

length OrgName $ 30;

 

Just select the longest length needed.

 

As others have stated, this is a questionable idea.  You might be better off changing your reporting technique instead of trying to combine the data sets.  For example, you could use customzied reporting:

 

data _null_;

set dataset1 (in=in1) dataset2 (in=in2);

by id;

file print;

if in1 then put ...

else if in2 then put ...

run;

 

The DATA step is actually a bit more complex than this, and you would need to look for examples of "cutomized reporting".  Good luck.

Solution
‎05-19-2016 07:20 AM
Contributor
Posts: 32

Re: How do we combine 2 datasets of different structures

Hi all, Thanks for your valuable inputs.. I know this was kind of a crazy question. But i had to generate a report which would result like this!..

 

How i did it is by exporting both the SAS datasets & then used Unix

 

cat dataset1 > dataset 2.. which did the trick for me!!!

Super User
Super User
Posts: 7,401

Re: How do we combine 2 datasets of different structures

Really, Unix can concatenate two SAS proprietary datasets, merging the headers and such like.  Thats amazing, I didn't think Unix even knew how SAS datasets were compiled?  Or do you mean you exported the data to text files and then concatenated?  If so why not just use one of the solutions above and skip that whole step.

Contributor
Posts: 32

Re: How do we combine 2 datasets of different structures

Yeah exported into flat files & concatinated !!!!

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 386 views
  • 0 likes
  • 5 in conversation