Help with a complicated reshape

JenniferBernard — Mon, 21 Dec 2020 21:21:34 GMT

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!

Re: Help with a complicated reshape

JenniferBernard — Tue, 22 Dec 2020 03:09:16 GMT

Great links, thanks so much

topic Re: Help with a complicated reshape in SAS Programming

Help with a complicated reshape

Re: Help with a complicated reshape

Re: Help with a complicated reshape