I'm trying to do a reshape to long a dataset that preserves info about firm ids and establishment ids. A person (id) can have multiple jobs at a time. I can transpose everything to long, but what I want looks more like this:
id_1 firmid1 estabid1 q1_value1 firmid2 estabid2 q1_value2 firmid3 estabid3 q1_value3
id_1 firmid1 estabid1 q2_value1 firmid2 estabid2 q2_value2 firmid3 estabid3 q2_value3
id_1 firmid1 estabid1 q3_value1 firmid2 estabid2 q3_value2 firmid3 estabid3 q3_value3
id_1 firmid1 estabid1 q4_value1 firmid2 estabid2 q4_value2 firmid3 estabid3 q4_value3
id_2 firmid1 estabid1 q1_value1 . . . . . .
id_2 firmid1 estabid1 q2_value1 . . . . . .
id_2 firmid1 estabid1 q3_value1 . . . . . .
id_2 firmid1 estabid1 q4_value1 . . . . . .
(In the example above, id_1 has three jobs, id_2 had one, etc.)
This dataset is HUGE... think q1-q124 and millions of ids, so trying to do it as efficiently as possible.
Is there a way to loop using first/last statements to make the code below work? If there is a better way to do this, I'm open to any ideas.
data have;
input id $10. firmid $5. estabid $4. q1 q2 q3 q4;
cards;
123456789 1001 000 100 125 125 150
123456789 1002 000 25 25 . .
123456789 1003 000 5 . . .
223456789 1002 000 25 25 25 25
323456789 1002 001 50 50 . .
423456789 1004 000 100 125 125 100
423456789 1005 001 5 . . .
523456789 1006 001 100 . 100 .
523456789 1006 002 . 100 . 100
;
run;
proc transpose data=have out=want2 prefix=earn name=qtr;
by id firmid estabid;
run;
data want2(drop=q q2);
set want2(rename=(qtr=q));
q2 = compress(q, ' ', 'A');
qtr=input(q2,8.);
run;
proc sort data=want2;
by id;
run;
data want3 (keep=id qtr firmid1 estabid1 firmid2 estabid2 firmid3 estabid3 earn_job1 earn_job2 earn_job3);
set want2;
by id;
length firmid1-firmid3 $5. estabid1-estabid3 $4. earn_job1-earn_job3 8;
retain firmid1 estabid1 firmid2 estabid2 firmid3 estabid3 earn_job1 earn_job2 earn_job3;
array afirmid{1:3} firmid1-firmid3;
array aestabid{1:3} estabid1-estabid3;
array aearn{1:3} earn_job1-earn_job3;
if first.id then do;
do i=1 to 3;
afirmid{i}=" ";
aestabid{i}=" ";
aearn{i}=.;
end;
end;
/*I am stuck here! Something like:
if qtr ge 1 then do;
afirmid{qtr}=firmid;
aestabid{qtr}=estabid;
aearn{qtr}=earn1;
end;
output;
This obviously doesn't work */
run;
Thanks for taking a look!
