topic Can this silly manually code be rewritten as Macro or do loop to make life easier? in SAS Programming

Can this silly manually code be rewritten as Macro or do loop to make life easier?

Seashore — Fri, 03 Jul 2015 04:43:22 GMT

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;

Re: Can this silly manually code be rewritten as Macro or do loop to make life easier?

Astounding — Fri, 03 Jul 2015 16:53:37 GMT

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.

Re: Can this silly manually code be rewritten as Macro or do loop to make life easier?

Reeza — Fri, 03 Jul 2015 16:59:19 GMT

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.

Re: Can this silly manually code be rewritten as Macro or do loop to make life easier?

Seashore — Fri, 03 Jul 2015 17:54:00 GMT

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

...

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

Re: Can this silly manually code be rewritten as Macro or do loop to make life easier?

RW9 — Fri, 03 Jul 2015 17:56:28 GMT

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;

Re: Can this silly manually code be rewritten as Macro or do loop to make life easier?

Reeza — Fri, 03 Jul 2015 17:56:31 GMT

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?