BookmarkSubscribeRSS Feed
Seashore
Calcite | Level 5

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;

5 REPLIES 5
Astounding
PROC Star

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.

Reeza
Super User

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.

Seashore
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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?

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 898 views
  • 1 like
  • 4 in conversation