proc sql noprint;
create table ds as
select * from all (keep=_NAME_ Col1-Col17 mom1) Comment: here is col1-col17, but when the "concyear" and concmonth changes, it
inner join b changes,too
on all.mom1=b.mom
where b.month=2 and b.cyear=1987; Comment: I have from 02/1987 to 12/2013, that's why I need easier way.
quit;
data ds1;
set ds;
array col{3} col15-col17; Comment: I only need 3 columns here. But now is 1987/02, if it is 1987/03, then I need
array post{3} post1-post3; col16-col18, etc.
do i=1 to dim(col);
post(i) = col(i);
end;
run;
data ds2;
set ds1;
array col{10} col5-col14; Comment: I need 10 columns totally here. now is col5-col14, if it is 1987/03, then it is
array g{10} g1-g10; col6-col15, etc.
do i=1 to dim(col);
g(i) = col(i);
end;
run;
data ds3; Comment: Actually I need 24 columns as maximum, but here I have already reached
set ds2;
array col{4} col1-col4; minimum, it will increase as the date increase. For example, 1987/03 will have col1-col5,
array pc{4} pc1-pc4; 1987/04 will have col1-col6. When it reaches 24 column as col1-col24, the next month will
do i=1 to dim(col); match col2-col25, and the count of column doesn't change any more
pc(i) = col(i);
end;
run;
data ds;
set ds3;
drop col1-col17 i; drop total number of the old "col" name
run;
Macros will not help. The silly part is not the manual coding, it's using 4 DATA steps, when 1 would do. It's analogous to coding:
data ds1;
set ds;
x=2;
y=5;
run;
data ds2;
set ds1;
z = x * y;
run;
data ds;
set ds2;
drop x y;
run;
You wouldn't think to use macros to simplify this (at least I hope not). Instead, you might code:
data ds;
set ds;
z = 2 * 5;
run;
The same goes for your code, even if the calculations are a bit more complex.
When you have data over time I recommend a long structure, so you can easily vary your dates required with a where/if clause. Then for final data set flip the data via proc transpose. No macros, no mess, no fuss.
But the part I would like to combine is the records...How could I still keep combining the records by transposing to long format?
my work.all data look like:
Col1(02/1987) Col2(03/1987) ...... Col300
A
B
C
D
E
F
...
Z
my work.b data look like:
1 A 02/1987
2 A 02/1987
3 A 05/1987
4 B 05/1987
...
100,000 Z 12/2013
I am afraid that doesn't give much information. post a datastep with some test data, and required output. Something like:
proc transpose data=have out=want;
var col1-col300;
by id;
run;
Your code doesn't show any 'combine' as far as I can see. What are you trying to combine and how are you defining combine?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.