How do we combine 2 datasets of different structures

[GunnerEP]

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.

[Kurt_Bremser]

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.

[GunnerEP]

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.

[Reeza]

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. 

[GunnerEP]

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). 

[RW9]

I agree with @Kurt_Bremser, 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;

[Reeza]

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. 

[Astounding]

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.

[GunnerEP · Accepted solution]

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!!!

[RW9]

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.

[GunnerEP]

Yeah exported into flat files & concatinated !!!!