Create a personalized table

[Demographer]

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:

Age	bd2001	bd2002	…	bd2046
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?

[Reeza]

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]

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

[Reeza]

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]

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]

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

[art297]

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;