topic Re: How do we combine 2 datasets of different structures in SAS Programming

How do we combine 2 datasets of different structures

GunnerEP — Wed, 18 May 2016 08:11:16 GMT

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.

Re: How do we combine 2 datasets of different structures

Kurt_Bremser — Wed, 18 May 2016 08:35:55 GMT

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.

Re: How do we combine 2 datasets of different structures

RW9 — Wed, 18 May 2016 09:13:34 GMT

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;

Re: How do we combine 2 datasets of different structures

Reeza — Wed, 18 May 2016 09:30:39 GMT

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.

Re: How do we combine 2 datasets of different structures

GunnerEP — Wed, 18 May 2016 09:32:37 GMT

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.

Re: How do we combine 2 datasets of different structures

Reeza — Wed, 18 May 2016 09:38:22 GMT

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.

Re: How do we combine 2 datasets of different structures

Astounding — Wed, 18 May 2016 09:44:36 GMT

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.

Re: How do we combine 2 datasets of different structures

GunnerEP — Wed, 18 May 2016 09:46:32 GMT

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

Re: How do we combine 2 datasets of different structures

GunnerEP — Thu, 19 May 2016 11:19:56 GMT

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

Re: How do we combine 2 datasets of different structures

RW9 — Thu, 19 May 2016 11:49:57 GMT

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.

Re: How do we combine 2 datasets of different structures

GunnerEP — Thu, 19 May 2016 14:06:21 GMT

Yeah exported into flat files & concatinated !!!!