Flatten Data

Ody — Fri, 16 Feb 2018 20:06:46 GMT

Good afternoon all,

I'm looking for some insight into the best way to flatten the following data. My goal is to join this data to another table where bin is the primary key. Currently, when I join on bin I'm getting repetitive results because of the one to many relationship.

data test;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;

datalines;
'123456' ,'GHG' ,'GED' ,'US ON 1' ,'560000' ,'1,000,000'
'123456' ,'GHG' ,'GED' ,'US ON 2' ,'560001' ,'1,000,001'
'123456' ,'GHG' ,'GED' ,'US ON 3' ,'560002' ,'1,000,002'
'123456' ,'GHG' ,'GED' ,'US ON 4' ,'560003' ,'1,000,003'
'123456' ,'GHG' ,'TRS' ,'US ON 1' ,'560004' ,'1,000,004'
'123456' ,'GHG' ,'TRS' ,'US ON 2' ,'560005' ,'1,000,005'
'123456' ,'GHG' ,'TRS' ,'US ON 3' ,'560006' ,'1,000,006'
;;;;;;

My assumption is that I need to break these data into separate data sets that look something like this. Is this the right solution or is there another method?

data test1;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'GED' ,'US ON 1' ,'560000' ,'1,000,000'
;

data test2;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'GED' ,'US ON 2' ,'560001' ,'1,000,001'
;

 
data test3;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;

datalines;
'123456' ,'GHG' ,'GED' ,'US ON 3' ,'560002' ,'1,000,002'
;

 
data test4;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'GED' ,'US ON 4' ,'560003' ,'1,000,003'
;

 
data test5;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'TRS' ,'US ON 1' ,'560004' ,'1,000,004'
;

data test6;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'TRS' ,'US ON 2' ,'560005' ,'1,000,005'
;

 
data test7;
infile datalines dsd truncover;
input
bin:$200.
field1:$200.
field2:$200.
field3:$200.
value1:$200.
value2:$200.     
;
datalines;
'123456' ,'GHG' ,'TRS' ,'US ON 3' ,'560006' ,'1,000,006'
;

Re: Flatten Data

ballardw — Fri, 16 Feb 2018 20:10:13 GMT

If your goal is to only include one set of values from data test, then select it.

It might help if showed one or two records from the other set and what the desired result of the join actually is.

However I doubt that creating N separate data sets to process is going to be an efficient or even easily understood approach.

topic Flatten Data in SAS Programming

Flatten Data

Re: Flatten Data