Solved: Complex merging when data in one dataset is in rows and the other is i...

Agent1592 · Posted 01-29-2020 07:43 PM

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

plevcek · Posted 01-29-2020 09:31 PM

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;

View solution in original post

plevcek · Posted 01-29-2020 09:31 PM

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;

r_behata · Posted 01-29-2020 10:26 PM

Show us how your expected output should look like.

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

Agent1592 · Posted 01-29-2020 10:32 PM

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

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

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

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

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

Click image to register for webinar

Classroom Training Available!