Problem with Proc transpose wide to long

[Barkat]

I have a table as below.

ID	State	Week	Var1	Var2	Var3
102292	CA	12/3/2022	OC		BT
102292	CA	12/31/2022	OC		BT
110422	PA	12/10/2022	OC		BT
110422	PA	12/10/2022	CR		BT
110423	VA	12/17/2022	OC		BT
110829	VA	12/24/2022	OC		BT
110874	TX	10/1/2022	OC		BT
110891	NV	1/7/2023	OC	CR	BT
110910	PA	1/7/2023	OC	CR	BT
110941	NC	12/3/2022	CR		BT
110951	SC	12/3/2022	OC		BT
110972	MA	10/22/2022	CR		BT

I used the code below to make it long (shown below the code) but failed. The values in the Var column displays var1, var2, var3 .... Also two new column is created named col1 and col2 that contains the values in the Var1-3 column in the original table.

proc sort data=have; by ID State Week; run;
Proc transpose data=have out=have_long;
by ID State Week ;
Var var1 var2 var3;
run;

ID	State	Week	Var
102292	CA	12/3/2022	OC
102292	CA	12/3/2022	BT
102292	CA	12/31/2022	OC
102292	CA	12/31/2022	BT
110422	PA	12/10/2022	OC
110422	PA	12/10/2022	BT
110422	PA	12/10/2022	CR
110422	PA	12/10/2022	BT
110423	VA	12/17/2022	OC
110423	VA	12/17/2022	BT
110829	VA	12/24/2022	OC
110829	VA	12/24/2022	BT
110874	TX	10/1/2022	OC
110874	TX	10/1/2022	BT
110891	NV	1/7/2023	OC
110891	NV	1/7/2023	CR
110891	NV	1/7/2023	BT
110910	PA	1/7/2023	OC
110910	PA	1/7/2023	CR
110910	PA	1/7/2023	BT
110941	NC	12/3/2022	CR
110941	NC	12/3/2022	BT
110951	SC	12/3/2022	OC
110951	SC	12/3/2022	BT
110972	MA	10/22/2022	CR
110972	MA	10/22/2022	BT

[andreas_lds · Accepted solution]

Again: Please post data in usable form, so that we have something to work with. Proc transpose struggles here, because the combination of Id, State and Week is not unique. Using a data step seems to be the easier way to solve the problem.

Untested:

data want;
   set have;
   
   length Var $ 2;
   
   array vars var1 - var3;
   
   do i = 1 to dim(vars);
      if not missing(vars[i]) then do;
         Var = vars[i];
         output;
      end;
   end;
   
   drop Var1 - Var3 i;
run;
      

[Barkat]

Wow! You made my day!