- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
set have(firstobs=2) ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.