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'
;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.