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 more