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
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;
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.
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.
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
;
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.
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;
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.
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: