Complex merging when data in one dataset is in rows and the other is in columns

Agent1592 — Thu, 30 Jan 2020 00:43:21 GMT

Dear SAS community:

I have two datasets. The first called "test1" is quarterly data for firms that have operations in certain states. If the value for the State Abbreviation is greater than 0 then the firm has operations in that state. The second dataset called test2 and has the states that were affected by some negative economic shock. I am trying to merge the two datasets and test is the leading dataset. I am interested in creating a dummy variable=1 if the firm has operations in certain states (State Abbreviation in the "test" dataset>0) and this state is affected by negative economic shock (stateid is in "test2" dataset). The dummy variable=0 if the firm has operations in certain states (state Abbreviation in the "test" dataset>0) and this state is not affected by negative economic shock (stateid is not in the second dataset) or if the firm has no operations in certain states (State variable in test=0) and this state is affected by negative economic shock (stateid is in the second dataset). It is almost like some kind of matrix multiplication task.

data WORK.TEST;
  infile datalines dsd truncover;
  input CID:$10. year:32. qtr:32. AL:32. AZ:32. AR:32. CA:32. CO:32. CT:32. DE:32. FL:32. GA:32. HI:32. ID:32. IL:32. IN:32. IA:32. KS:32. KY:32. LA:32. ME:32. MD:32. MA:32. MI:32. MN:32. MS:32. MO:32. MT:32. NE:32. NV:32. NH:32. NJ:32. NM:32. NY:32. NC:32. ND:32. OH:32. OK:32. OR:32. PA:32. RI:32. SC:32. SD:32. TN:32. TX:32. UT:32. VT:32. VA:32. WA:32. WV:32. WI:32. WY:32.;
  label CID="Company ID";
datalines4;
0000061478,2005,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,8,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000061478,2005,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,8,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000061478,2005,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,8,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000061478,2005,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,8,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000006201,2005,1,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,12,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000006201,2005,2,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,12,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000006201,2005,3,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,12,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000006201,2005,4,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,12,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000065695,2005,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,1,0,0,0,1,0,5,0
0000065695,2005,2,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,1,0,0,0,1,0,5,0
0000065695,2005,3,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,1,0,0,0,1,0,5,0
0000065695,2005,4,0,0,0,0,0,0,1,0,0,0,0,0,0,0,1,0,1,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,1,0,0,0,1,0,5,0
0000706688,2005,1,0,0,0,1,0,0,0,4,11,0,0,0,2,0,0,0,0,0,2,0,0,0,2,0,0,0,0,0,0,0,0,1,0,2,4,0,0,0,0,0,2,4,0,0,0,0,0,0,0
0000706688,2005,2,0,0,0,1,0,0,0,4,11,0,0,0,2,0,0,0,0,0,2,0,0,0,2,0,0,0,0,0,0,0,0,1,0,2,4,0,0,0,0,0,2,4,0,0,0,0,0,0,0
0000706688,2005,3,0,0,0,1,0,0,0,4,11,0,0,0,2,0,0,0,0,0,2,0,0,0,2,0,0,0,0,0,0,0,0,1,0,2,4,0,0,0,0,0,2,4,0,0,0,0,0,0,0
0000706688,2005,4,0,0,0,1,0,0,0,4,11,0,0,0,2,0,0,0,0,0,2,0,0,0,2,0,0,0,0,0,0,0,0,1,0,2,4,0,0,0,0,0,2,4,0,0,0,0,0,0,0
0000001800,2005,1,0,0,0,8,1,0,0,0,0,0,0,12,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,3,0,0,0,0,2,0,0,0,0,0,0,0,2,0,0,1,0,0,0,0
0000001800,2005,2,0,0,0,8,1,0,0,0,0,0,0,12,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,3,0,0,0,0,2,0,0,0,0,0,0,0,2,0,0,1,0,0,0,0
0000001800,2005,3,0,0,0,8,1,0,0,0,0,0,0,12,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,3,0,0,0,0,2,0,0,0,0,0,0,0,2,0,0,1,0,0,0,0
0000001800,2005,4,0,0,0,8,1,0,0,0,0,0,0,12,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,3,0,0,0,0,2,0,0,0,0,0,0,0,2,0,0,1,0,0,0,0
0000002488,2005,1,0,0,0,11,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000002488,2005,2,0,0,0,11,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000002488,2005,3,0,0,0,11,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000002488,2005,4,0,0,0,11,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000350200,2005,1,0,0,0,0,0,0,2,0,0,0,0,2,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,0,0,0,0,14,0,0,0,0,0,0,0,0,0,6,6,0
0000350200,2005,2,0,0,0,0,0,0,2,0,0,0,0,2,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,0,0,0,0,14,0,0,0,0,0,0,0,0,0,6,6,0
0000350200,2005,3,0,0,0,0,0,0,2,0,0,0,0,2,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,0,0,0,0,14,0,0,0,0,0,0,0,0,0,6,6,0
0000350200,2005,4,0,0,0,0,0,0,2,0,0,0,0,2,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,0,0,0,0,14,0,0,0,0,0,0,0,0,0,6,6,0
0001122304,2005,1,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,5,0,0,0,0,1,0,0,0,0,0,0,0
0001122304,2005,2,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,5,0,0,0,0,1,0,0,0,0,0,0,0
0001122304,2005,3,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,5,0,0,0,0,1,0,0,0,0,0,0,0
0001122304,2005,4,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,5,0,0,0,0,1,0,0,0,0,0,0,0
0000002969,2005,1,1,0,0,6,0,0,0,2,0,0,0,0,2,0,3,1,3,0,1,0,0,0,1,0,0,0,0,0,2,0,0,0,0,0,0,0,5,0,1,0,1,5,0,0,0,1,0,0,0
0000002969,2005,2,1,0,0,6,0,0,0,2,0,0,0,0,2,0,3,1,3,0,1,0,0,0,1,0,0,0,0,0,2,0,0,0,0,0,0,0,5,0,1,0,1,5,0,0,0,1,0,0,0
0000002969,2005,3,1,0,0,6,0,0,0,2,0,0,0,0,2,0,3,1,3,0,1,0,0,0,1,0,0,0,0,0,2,0,0,0,0,0,0,0,5,0,1,0,1,5,0,0,0,1,0,0,0
0000002969,2005,4,1,0,0,6,0,0,0,2,0,0,0,0,2,0,3,1,3,0,1,0,0,0,1,0,0,0,0,0,2,0,0,0,0,0,0,0,5,0,1,0,1,5,0,0,0,1,0,0,0
0000003146,2005,1,16,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0,0,6,0,0,0,0,0,0,0
0000003146,2005,2,16,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0,0,6,0,0,0,0,0,0,0
0000003146,2005,3,16,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0,0,6,0,0,0,0,0,0,0
0000003146,2005,4,16,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0,0,6,0,0,0,0,0,0,0
0000003153,2005,1,125,0,0,1,0,0,2,23,195,0,0,0,0,0,0,0,1,2,0,0,0,0,78,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,1,0,1,0,0,0,1,2,0,0,0
0000003153,2005,2,125,0,0,1,0,0,2,23,195,0,0,0,0,0,0,0,1,2,0,0,0,0,78,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,1,0,1,0,0,0,1,2,0,0,0
0000003153,2005,3,125,0,0,1,0,0,2,23,195,0,0,0,0,0,0,0,1,2,0,0,0,0,78,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,1,0,1,0,0,0,1,2,0,0,0
0000003153,2005,4,125,0,0,1,0,0,2,23,195,0,0,0,0,0,0,0,1,2,0,0,0,0,78,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,1,0,1,0,0,0,1,2,0,0,0
0000098618,2005,1,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,1,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000098618,2005,2,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,1,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000098618,2005,3,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,1,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000098618,2005,4,0,0,0,0,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,1,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000701288,2005,1,5,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000701288,2005,2,5,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000701288,2005,3,5,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000701288,2005,4,5,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,0
0000003333,2005,1,0,0,2,62,4,2,4,6,0,0,8,6,4,2,2,0,8,4,2,6,2,2,0,2,2,6,8,2,4,2,0,0,2,0,4,6,6,2,0,2,4,12,6,2,0,6,0,2,2
0000003333,2005,2,0,0,2,62,4,2,4,6,0,0,8,6,4,2,2,0,8,4,2,6,2,2,0,2,2,6,8,2,4,2,0,0,2,0,4,6,6,2,0,2,4,12,6,2,0,6,0,2,2
0000003333,2005,3,0,0,2,62,4,2,4,6,0,0,8,6,4,2,2,0,8,4,2,6,2,2,0,2,2,6,8,2,4,2,0,0,2,0,4,6,6,2,0,2,4,12,6,2,0,6,0,2,2
0000003333,2005,4,0,0,2,62,4,2,4,6,0,0,8,6,4,2,2,0,8,4,2,6,2,2,0,2,2,6,8,2,4,2,0,0,2,0,4,6,6,2,0,2,4,12,6,2,0,6,0,2,2
0000003453,2005,1,0,0,0,6,2,0,0,0,0,68,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,0,0,2,0,0,1,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000003453,2005,2,0,0,0,6,2,0,0,0,0,68,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,0,0,2,0,0,1,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000003453,2005,3,0,0,0,6,2,0,0,0,0,68,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,0,0,2,0,0,1,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000003453,2005,4,0,0,0,6,2,0,0,0,0,68,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,0,0,2,0,0,1,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0
0000354767,2005,1,9,0,0,5,5,0,0,0,0,0,0,0,0,0,5,0,8,0,0,0,15,0,1,0,22,0,0,0,0,0,0,0,0,0,13,0,0,0,0,0,0,32,0,0,0,0,0,0,6
0000354767,2005,2,9,0,0,5,5,0,0,0,0,0,0,0,0,0,5,0,8,0,0,0,15,0,1,0,22,0,0,0,0,0,0,0,0,0,13,0,0,0,0,0,0,32,0,0,0,0,0,0,6
0000354767,2005,3,9,0,0,5,5,0,0,0,0,0,0,0,0,0,5,0,8,0,0,0,15,0,1,0,22,0,0,0,0,0,0,0,0,0,13,0,0,0,0,0,0,32,0,0,0,0,0,0,6
0000354767,2005,4,9,0,0,5,5,0,0,0,0,0,0,0,0,0,5,0,8,0,0,0,15,0,1,0,22,0,0,0,0,0,0,0,0,0,13,0,0,0,0,0,0,32,0,0,0,0,0,0,6
0000003545,2005,1,0,0,0,1,0,0,0,39,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000003545,2005,2,0,0,0,1,0,0,0,39,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000003545,2005,3,0,0,0,1,0,0,0,39,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000003545,2005,4,0,0,0,1,0,0,0,39,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000775368,2005,1,0,0,0,19,1,2,2,1,2,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,4,0,0,4,3,1,0,0,1,0,0,0,0,0,0,1,1,0,0,12,0,2,1
0000775368,2005,2,0,0,0,19,1,2,2,1,2,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,4,0,0,4,3,1,0,0,1,0,0,0,0,0,0,1,1,0,0,12,0,2,1
0000775368,2005,3,0,0,0,19,1,2,2,1,2,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,4,0,0,4,3,1,0,0,1,0,0,0,0,0,0,1,1,0,0,12,0,2,1
0000775368,2005,4,0,0,0,19,1,2,2,1,2,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,4,0,0,4,3,1,0,0,1,0,0,0,0,0,0,1,1,0,0,12,0,2,1
0000003673,2005,1,0,0,0,4,0,4,4,0,0,0,0,3,5,0,0,0,0,0,55,0,0,0,1,0,0,0,2,0,5,1,6,0,0,34,0,0,54,0,0,0,4,1,0,0,34,0,75,0,2
0000003673,2005,2,0,0,0,4,0,4,4,0,0,0,0,3,5,0,0,0,0,0,55,0,0,0,1,0,0,0,2,0,5,1,6,0,0,34,0,0,54,0,0,0,4,1,0,0,34,0,75,0,2
0000003673,2005,3,0,0,0,4,0,4,4,0,0,0,0,3,5,0,0,0,0,0,55,0,0,0,1,0,0,0,2,0,5,1,6,0,0,34,0,0,54,0,0,0,4,1,0,0,34,0,75,0,2
0000003673,2005,4,0,0,0,4,0,4,4,0,0,0,0,3,5,0,0,0,0,0,55,0,0,0,1,0,0,0,2,0,5,1,6,0,0,34,0,0,54,0,0,0,4,1,0,0,34,0,75,0,2
0000003982,2005,1,0,0,0,0,10,0,3,0,0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,7,0,0,0,0,0,0,59,0,0,0,0,0,0,0
0000003982,2005,2,0,0,0,0,10,0,3,0,0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,7,0,0,0,0,0,0,59,0,0,0,0,0,0,0
0000003982,2005,3,0,0,0,0,10,0,3,0,0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,7,0,0,0,0,0,0,59,0,0,0,0,0,0,0
0000003982,2005,4,0,0,0,0,10,0,3,0,0,0,0,0,0,0,0,0,7,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,7,0,0,0,0,0,0,59,0,0,0,0,0,0,0
0000004164,2005,1,4,0,0,6,0,0,1,0,2,0,0,0,9,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000004164,2005,2,4,0,0,6,0,0,1,0,2,0,0,0,9,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000004164,2005,3,4,0,0,6,0,0,1,0,2,0,0,0,9,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000004164,2005,4,4,0,0,6,0,0,1,0,2,0,0,0,9,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0
0000004344,2005,1,2,0,2,0,0,0,1,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,4,0,0,0,0,0,0,2,0,2,4,0,0,0,1,0,0,0
0000004447,2005,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000004447,2005,2,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000004447,2005,3,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000004447,2005,4,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0
0000701345,2005,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,1,0,0,4,0
0000701345,2005,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,1,0,0,4,0
0000701345,2005,3,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,1,0,0,4,0
0000701345,2005,4,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,1,0,0,1,0,0,4,0
0000004515,2005,1,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,10,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000004515,2005,2,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,10,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000004515,2005,3,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,10,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000004515,2005,4,0,2,0,3,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,10,0,0,0,2,0,0,0,0,0,0,3,0,0,0,2,0,0,0
0000771497,2005,1,0,0,0,27,2,0,2,6,1,2,0,0,0,0,0,0,0,0,1,0,0,2,0,0,0,2,0,0,0,0,13,0,0,0,0,2,3,0,0,0,0,8,0,0,1,5,0,0,0
0000771497,2005,2,0,0,0,27,2,0,2,6,1,2,0,0,0,0,0,0,0,0,1,0,0,2,0,0,0,2,0,0,0,0,13,0,0,0,0,2,3,0,0,0,0,8,0,0,1,5,0,0,0
0000771497,2005,3,0,0,0,27,2,0,2,6,1,2,0,0,0,0,0,0,0,0,1,0,0,2,0,0,0,2,0,0,0,0,13,0,0,0,0,2,3,0,0,0,0,8,0,0,1,5,0,0,0
;;;;

data WORK.TEST2;
  infile datalines dsd truncover;
  input Year:32. Quarter:32. state_id:$2.;
datalines4;
2005,3,AL
2005,3,AR
2005,3,CT
2005,3,FL
2005,3,GA
2005,3,IA
2005,3,IL
2005,3,IN
2005,3,KS
2005,3,KY
2005,3,LA
2005,3,MA
2005,3,MD
2005,3,ME
2005,3,MI
2005,3,MO
2005,3,MS
2005,3,NC
2005,3,NH
2005,3,NJ
2005,3,NY
2005,3,OH
2005,3,PA
2005,3,RI
2005,3,SC
2005,3,TN
2005,3,TX
2005,3,VA
2005,3,VT
2005,3,WV
;;;;

Re: Complex merging when data in one dataset is in rows and the other is in columns

plevcek — Thu, 30 Jan 2020 02:31:48 GMT

This is the approach I used.

From the TEST dataset create a long table (transposed) and then compare it with TEST2.

I created table only for those impacted as otherwise I was assuming variable=0. You can change criteria if I missed something but it gives you one of the ideas.

*sort so that we can transpose;
proc sort data=test;
by cid year qtr;

*create a long table instead of wide;
proc transpose data=test out=test5(rename=(_name_=state_id col1=state_value));
by cid year qtr;
var AL AZ AR CA CO CT DE FL GA HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT 
		NE NV NH NJ NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY;

*those that have been affected by economy;
DATA test6;
SET test5;
FORMAT year quarter 4. state_id $2.;
  IF _n_ = 1 THEN DO; 
    DECLARE hash ht(DATASET:'test2');
    ht.defineKey('year','quarter','state_id');
    ht.defineDone();
  END;

	rc = ht.check(key:year, key:qtr, key:state_id);

	*your criteria for those where value > 0 and impacted by economy;
	IF rc=0 AND state_value > 0; 
	variable = 1;
DROP rc quarter;
RUN;

*get unique companies for each year,quarter;
PROC SQL; CREATE TABLE test7 AS SELECT distinct cid, year, qtr, variable FROM test6;

*merge back those that have been affected;
PROC SQL; CREATE TABLE test_final AS 
SELECT a.*, COALESCE(b.variable,0) as variable 
FROM test a 
	LEFT JOIN test7 b 
		ON a.cid=b.cid AND a.year=b.year AND a.qtr=b.qtr;

Re: Complex merging when data in one dataset is in rows and the other is in columns

r_behata — Thu, 30 Jan 2020 03:26:13 GMT

Show us how your expected output should look like.

Do you want multiple dummy variables if the same firm has presence in multiple states?

Re: Complex merging when data in one dataset is in rows and the other is in columns

Agent1592 — Thu, 30 Jan 2020 03:32:24 GMT

No I need just one dummy variable. The output should be the same as the test table plus one more dummy.

topic Re: Complex merging when data in one dataset is in rows and the other is in columns in SAS Programming

Complex merging when data in one dataset is in rows and the other is in columns

Re: Complex merging when data in one dataset is in rows and the other is in columns

Re: Complex merging when data in one dataset is in rows and the other is in columns

Re: Complex merging when data in one dataset is in rows and the other is in columns