Help using Base SAS procedures

Create a personalized table

Reply
Frequent Contributor
Posts: 113

Create a personalized table

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?

Super User
Posts: 19,870

Re: Create a personalized table

Posted in reply to Demographer

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

Frequent Contributor
Posts: 113

Re: Create a personalized table

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

Super User
Posts: 19,870

Re: Create a personalized table

Posted in reply to Demographer

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?

Super User
Posts: 11,343

Re: Create a personalized table

Posted in reply to Demographer

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.

Super User
Posts: 5,518

Re: Create a personalized table

Posted in reply to Demographer

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

PROC Star
Posts: 7,492

Re: Create a personalized table

Posted in reply to Demographer

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=_Smiley Happy;

  by age;

  id name;

  idlabel name;

  var count;

run;

Ask a Question
Discussion stats
  • 6 replies
  • 211 views
  • 3 likes
  • 5 in conversation