How to change many columns to two columns

[yotsuba88]

Hi guys,

I want the data like below and want to move to same columns ( a, a) to one column name and 2 columns of data and number. I tried to add them into one column and extract later but the order is not correct. Please help and advise. Thank you so much,

Ha

[novinosrin]

Hi @yotsuba88   You could also consider lazy folks like me who would request you to paste the values(sample) as plain text so we can easily copy paste rather than pictures plz. 

[yotsuba88]

a	a 1	b	b1	c	c1
1/01/2019	4	3/01/2018	12	10/05/2000	111
2/01/2019	8	4/01/2018	13	11/05/2000	112
3/01/2019	9	5/01/2018	14	12/05/2000	113
a	1/01/2019	4
a	2/01/2019	8
a	3/01/2019	9
b	3/01/2018	12
b	4/01/2018	13
b	5/01/2018	14
b	10/05/2000	111
b	11/05/2000	112
b	12/05/2000	113

Hi, Thank you so much for your reply. Sorry Now I copy and paste here and else attach file in case. 

[novinosrin]

Thank you @yotsuba88   One more clarification plz. No more than one variable can have the same variable(column) names. I suppose your series can be like a,a1, b, b1

So a real representative would like this to make sense?

data have;
input a :mmddyy10.	 a1 	b	:mmddyy10. b1	c :mmddyy10.	c1;
format a b c mmddyy10.;
lines;
1/01/2019	4	3/01/2018	12	10/05/2000	111
2/01/2019	8	4/01/2018	13	11/05/2000	112
3/01/2019	9	5/01/2018	14	12/05/2000	113
;

[yotsuba88]

Yes, I changed my previous msg, but I dont know how to write to output. And I have thousands columns of a, a1, b,b2, when I add them up in one column, it has 1,5 million rows. So I cannot export to excel and do manually. 

[novinosrin · Accepted solution]

Hi @yotsuba88  Keeping it simple

data have;
input a :mmddyy10.	 a1 	b	:mmddyy10. b1	c :mmddyy10.	c1;
format a b c mmddyy10.;
lines;
1/01/2019	4	3/01/2018	12	10/05/2000	111
2/01/2019	8	4/01/2018	13	11/05/2000	112
3/01/2019	9	5/01/2018	14	12/05/2000	113
;

data temp;
set have;
array t a--c1;
do i=1 by 2 to dim(t) ;
 group=first(vname(t(i)));
 date=t(i);
 value=t(i+1);
 output;
end;
keep group date value;
format date mmddyy10.;
run;

proc sort data=temp out=want;
by group date;
run;

[yotsuba88]

Hi novinosrin,

I really appreciate your help. It seems working for me now and I have to format the date manually on my excel to run this. I need to double check and accept as solution. 

[Shmuel]

Please clarify:

 If there are 1277 variables then it is not a pair number of columns.

 What are the real variable names? what unique variable(s) exist?

According to your code:

array t {1277} AU000000CAA9_1--US9888581066;

It seems there are more than one unique variable  - is it ?

At what point start the pair columns ? are they in middle or at start or at end of a row?

Please post a sample of your data - include some unique variables and several pairs - in same order you have them.

[VDD]

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[yotsuba88]

THank you so much, I checking on it now.

[Shmuel]

I suspect that your data origin is an excel file.

Have you imported it? what variables do you have in the sas dataset?

Assuming the variables are: var1 var2 ... var6

you can either use the proc transpose, as proposed by @VDD or use next code:

data a;
  set have(keep = var1 var2);
run;
data b;
 set have(keep=var3 var4);
      rename var3=var1 var4=var2;
run;
data c;
 set have(keep=var5 var6)
     rename var5=var1 var6=var2;
run;

data want;
  set a(in=ina)
        b(in=inb)
        c(in=inc)
;
  if ina then id='a'; else
  if inb then id='b'; else id='c';
run;

[yotsuba88]

Yes, I import from Excel to SAS. Thank you, I am checking the code now

[Shmuel]

You may need to skip first obs by:
set have(firstobs=2) ...

[yotsuba88]

Hi Shmuel,

Thank you so much, your code makes sense to me but I have thousand of columns so I dont think it is an efficient way.

[Patrick]

If you still need a solution then may be attach a copy of your Excel with a few rows in it so we can understand your reality.

...and for the folks who don't open Excels: May be also store this sample as .csv and attach it as well.

If you've got Enterprise Guide: You could also use the import wizard for the Excel and tick to include the data. Then save the generated code as .sas file and attach this .sas file here.