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?
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 25. 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.