topic Re: How to change many columns to two columns in SAS Programming

How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 14:54:43 GMT

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,

Re: How to change many columns to two columns

novinosrin — Wed, 16 Oct 2019 14:58:19 GMT

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.

Re: How to change many columns to two columns

VDD — Wed, 16 Oct 2019 15:08:10 GMT

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

Re: How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 15:49:31 GMT

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.

Re: How to change many columns to two columns

Shmuel — Wed, 16 Oct 2019 15:46:29 GMT

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;

Re: How to change many columns to two columns

novinosrin — Wed, 16 Oct 2019 15:46:33 GMT

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
;

Re: How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 15:57:00 GMT

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.

Re: How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 15:51:16 GMT

THank you so much, I checking on it now.

Re: How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 15:51:56 GMT

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

Re: How to change many columns to two columns

Shmuel — Wed, 16 Oct 2019 15:53:29 GMT

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

Re: How to change many columns to two columns

yotsuba88 — Wed, 16 Oct 2019 15:55:16 GMT

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.

Re: How to change many columns to two columns

novinosrin — Wed, 16 Oct 2019 15:58:12 GMT

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;

Re: How to change many columns to two columns

yotsuba88 — Thu, 17 Oct 2019 05:33:51 GMT

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.

Re: How to change many columns to two columns

Shmuel — Thu, 17 Oct 2019 05:49:14 GMT

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.

Re: How to change many columns to two columns

Patrick — Thu, 17 Oct 2019 07:18:18 GMT

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.