topic Re: Create a personalized table in SAS Procedures

Create a personalized table

Demographer — Wed, 09 Jan 2013 01:42:41 GMT

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?

Re: Create a personalized table

Reeza — Wed, 09 Jan 2013 01:59:38 GMT

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...

Re: Create a personalized table

ballardw — Wed, 09 Jan 2013 15:38:30 GMT

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.

Re: Create a personalized table

Demographer — Wed, 09 Jan 2013 20:09:42 GMT

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

Re: Create a personalized table

Reeza — Wed, 09 Jan 2013 20:13:49 GMT

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?

Re: Create a personalized table

Astounding — Wed, 09 Jan 2013 20:44:04 GMT

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

Re: Create a personalized table

art297 — Wed, 09 Jan 2013 20:51:15 GMT

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;