DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 7

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

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;

Super User
Posts: 5,085

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

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.

Super User
Posts: 17,863

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

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.

Occasional Contributor
Posts: 7

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

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

Super User
Super User
Posts: 7,407

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

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;

Super User
Posts: 17,863

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

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?

Ask a Question
Discussion stats
  • 5 replies
  • 271 views
  • 1 like
  • 4 in conversation