BookmarkSubscribeRSS Feed
Demographer
Pyrite | Level 9

Hi,

I have 46 tables created with proc frec on the same variable (age, that is a continous variable going from 0 to 117, with no decimal), but from different files (bd2001-bd2046).

I did it this way:

%macro loop(year1, year2);

   %local year;

   %do year = &year1 %to &year2;

          proc freq data=bd.bd&year;

          table age;

          run;

   %end;

%mend loop;

%loop(2001, 2045);

I want to create a new table that would merge those 46 tables and that would looks like this:

Agebd2001bd2002bd2046
0######
1######
2######
117######

If I add an out statement with proc freq and merge resulting tables with data set, the problem I meet is that some categories could not fit. For example, if there is no age=52 only for the data set bd2009, then the cell age=53 would take its place and the missing category would be set to age=117. How can I solve this problem?

6 REPLIES 6
Reeza
Super User

You could change how you're doing it...assuming you don't have huge datasets.

If you do, make sure you're merging using a by statement, by age to allow the merge to happen appropriately.

data test;

set bd2001-bd2046 indsname=source;

name=scan(source, 2, ".");

run;

proc freq data=test noprint;

table name*age/out=test2;

run;

*then transpose test2...

Demographer
Pyrite | Level 9

Thank you, but I have very huge dataset and very slow computer. Any other way?

Reeza
Super User

You don't actually show the code that's giving you the issue. If you merge with by statements you shouldn't have that issue. Can you provide the code that's giving the issue?

ballardw
Super User

Or using Reeza's TEST data set:

Proc tabulate data=test;

     class age name;

     table age='',name='' * n=''

             /box='Age';

run;

By default the count (n) will have decimals. You can apply a format with n=''*f=f6. or n=''*f=comma6. or similar depening on range of the counts.

Astounding
PROC Star

It sounds like you forgot to add the BY AGE statement after your MERGE statement.

art297
Opal | Level 21

I must have missed something here, as I've seen the merge statement mentioned a couple of times, but haven't seen it in any of the code.

If your files are large because they contain variables that aren't needed for the current process, you could use Fareeza's suggested code but only keep the age variable.  i.e.:

data test;

  set bd2001-bd2046 (keep=age) indsname=source;

  name=scan(source, 2, ".");

run;

proc freq data=test noprint;

  table age*name/out=test2 (keep= name age count);

run;

proc transpose data=test2

  out=want (drop=_:);

  by age;

  id name;

  idlabel name;

  var count;

run;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 716 views
  • 3 likes
  • 5 in conversation