topic Re: macro help in SAS Programming

macro help

supersonic — Tue, 20 Aug 2013 06:29:51 GMT

I have a dataset 'test' as

var1 var2 var3 var4....varn

abcd M 22 1500...

qwerty M 32 5000...

asdf F 21 100...

tyuif F 33 6500..

jklmn M 45 800..

fghtn F 18 4000..

fgtn F 28 4050..

abcd M 22 1500..

abcde M 24 1500..

I need to get the output as

for only numeric variables

Header 1	Header 2	Header 3	Header 4	Header 5
Variable	Min	Max	Mean	Std
Var3
Var4
...
Varn

for only character variables

Header 1	Header 2	Header 3	Header 4
Variable	Mode	Freqency of Mode	No:of distinct varaibles
var1
var2
..
varn

Re: macro help

supersonic — Tue, 20 Aug 2013 06:32:56 GMT

The first part can be done using proc means.

Please help to solve the 2nd part

Re: macro help

Fugue — Tue, 20 Aug 2013 06:38:55 GMT

Have you taken a look at PROC TRANSPOSE?

Re: macro help

supersonic — Tue, 20 Aug 2013 07:18:12 GMT

no i havent tried.

But whether proc transpose will give me the mode , freq of mode and distinct count?

Re: macro help

supersonic — Tue, 20 Aug 2013 12:52:42 GMT

any clue?

Re: macro help

Murray_Court — Tue, 20 Aug 2013 13:46:27 GMT

This should work, I have solved for the numeric variables and left the char one as an exercise.

options symbolgen mlogic mprint; /* for debugginf macros, but the good stuff into the log */

data test; /* example dataset */

input string $ 1-4 gender $6 age 8-9 stat 10-14;

datalines;

abcd M 22 1500

qwer M 32 5000

asdf F 21 100

tyui F 33 6500

jklm M 45 800

fght F 18 4000

fgtn F 28 4050

;

data nums; /*subset numeric*/

set test(keep=_numeric_);

run;

data chars;/*subset character*/

set test(keep=_character_);

run;

/*get list of numeric variable names, shove them into a macro */

proc transpose data=nums out=numst;

run;

proc sql;

select _NAME_ into :numvars seperated by "*" from numst;

quit;

%let cardnumvars= %sysfunc(countw(&numvars.,*));

%put numvars.=&numvars;

%put cardnumvars.=&cardnumvars.;

%macro numstats;

proc sql;

/*for each numeric variable create table with the stats */

%do i=1 %to &cardnumvars.;

%let var = %scan(&numvars.,&i.);

create table num&i. as

select

"&var " as variable, /* hacking to stop truncation of name variable, needs better fix for general use */

min(&var) as min,

max(&var) as max,

avg(&var) as mean,

std(&var) as std

from nums

;

%end;

quit;

%mend numstats;

%numstats

/*put all the small stats tables together */

%macro lister;

%do i=1 %to &cardnumvars.;

num&i.

%end;

%mend lister;

data numstats;

set %lister ;

run;

/* print the output */

proc print data=numstats;

run;

/* similar proccess can be used for character variables, left as exercise */

Re: macro help

Astounding — Tue, 20 Aug 2013 14:13:08 GMT

Here's one way to get the Frequency of Mode and Number of Values for a single variable. There are other methods, such as SQL. I leave it up to you to get them into your report properly.

proc freq data=have order=freq;

tables var1 / noprint out=var1;

run;

data want;

set var1 (obs=1) nobs=_nobs_;

mode = var1;

frequency_of_mode = count;

number_of_distinct_values = _nobs_;

run;

Also note that PROC MEANS can compute the MODE. However, you may have to make a decision about what to report if two values are tied for the highest frequency.

Good luck.

Re: macro help

supersonic — Wed, 21 Aug 2013 09:15:12 GMT

Thank you Astounding and Murray_Court