topic Re: dynamic code in SAS Programming

dynamic code

rohithverma — Fri, 15 May 2020 12:13:01 GMT

Hi All,

i have a input data as below

DATE CODE

10-MAY-2020 G2345
10-MAY-2020 AUTO
11-MAY-2020 G2345
11-MAY-2020 TOP
11-MAY-2020 TOP

i need output as like below but the code must be dynamic .

DATE G2345 TOP AUTO
10-MAY-2020 1 0 1
11-MAY-2020 1 2 0

Re: dynamic code

PeterClemmensen — Fri, 15 May 2020 12:17:56 GMT

Is this for reporting purposes?

Re: dynamic code

rohithverma — Fri, 15 May 2020 12:20:22 GMT

yes ,automaticallty the output must be creatyed

Re: dynamic code

RichardDeVen — Fri, 15 May 2020 12:41:50 GMT

Output can be a report file

ODS HTML
+ Proc TABULATE

or, a data file such as a sas data set

Proc FREQ
+ TRANSPOSE
+ DATA step clean up

Example:

data have;
input DATE date11. CODE $; format date date11.; datalines;
10-MAY-2020         G2345
10-MAY-2020          AUTO
11-MAY-2020         G2345
11-MAY-2020          TOP
11-MAY-2020          TOP
;

* As a report;
ods html file='output.html' style=plateau;
proc tabulate data=have;
  CLASS DATE CODE;
  TABLE DATE,CODE;
run;
ods html close;

* As a data set;
proc sql;
  create table counts as select date,code,count(*) as N from have group by date,code;
proc transpose data=counts out=stage1(drop=_name_);
  by date;
  id code;
  var N;
run;
data want;
  set stage1;
  array codeN _numeric_;
  do _n_ = 1 to dim(codeN);
    codeN(_n_) = coalesce(codeN(_n_),0);
  end;
run;

Re: dynamic code

yabwon — Fri, 15 May 2020 12:41:07 GMT

Hi,

simplest would be Proc Tabulate:

data have;
input DATE date11. CODE $;
format date date11.;
cards;
10-MAY-2020         G2345
10-MAY-2020          AUTO
11-MAY-2020         G2345
11-MAY-2020          TOP
11-MAY-2020          TOP
;
run;

proc format;
value missingzero
. = 0
other = [best32.]
;
run;
proc tabulate data = have missing;
class date code;
table date, code*n*f=missingzero.;
run;

Bart

Re: dynamic code

PeterClemmensen — Fri, 15 May 2020 12:41:07 GMT

data have;
input DATE :date11. CODE $;
format DATE date11.;
datalines;
10-MAY-2020 G2345
10-MAY-2020 AUTO
11-MAY-2020 G2345
11-MAY-2020 TOP
11-MAY-2020 TOP
;

proc tabulate data=have;
    class date code;
    table date,code*n=' ';
run;

Re: dynamic code

novinosrin — Fri, 15 May 2020 12:44:36 GMT

data have;
input DATE  :date11.   CODE $;
format date date11.;
cards;
10-MAY-2020         G2345
10-MAY-2020          AUTO
11-MAY-2020         G2345
11-MAY-2020          TOP
11-MAY-2020          TOP
;

proc freq data=have ;
tables date*code/noprint sparse out=temp(drop=percent);
run;

proc transpose data=temp out=want(drop=_:);
by date;
id code;
var count;
run;

Re: dynamic code

rohithverma — Fri, 15 May 2020 13:16:57 GMT

I NEED OUPUT LIKE THIS

DATE G2345 TOP AUTO TOTAL
10-MAY-2020 1 0 1 2
11-MAY-2020 1 2 0 3
GRANDTOTAL 2 2 1 5

Re: dynamic code

rohithverma — Fri, 15 May 2020 13:17:20 GMT

Sorry but i am unable to generate like below

DATE G2345 TOP AUTO TOTAL
10-MAY-2020 1 0 1 2
11-MAY-2020 1 2 0 3
GRANDTOTAL 2 2 1 5

Re: dynamic code

yabwon — Fri, 15 May 2020 13:20:13 GMT

Use `ALL` keyword:

data have;
input DATE date11. CODE $;
format date date11.;
cards;
10-MAY-2020         G2345
10-MAY-2020          AUTO
11-MAY-2020         G2345
11-MAY-2020          TOP
11-MAY-2020          TOP
;
run;

proc format;
value missingzero
. = 0
other = [best32.]
;
run;
proc tabulate data = have missing;
class date code;
table date ALL, (code ALL)*n*f=missingzero.;
run;

and read doc abut Proc Tabulate:

https://documentation.sas.com/?docsetId=proc&docsetTarget=n00yutbvvckjwrn1ldg5xkvjy1pu.htm&docsetVersion=9.4&locale=en

Bart

Re: dynamic code

PeterClemmensen — Fri, 15 May 2020 13:20:39 GMT

Try this

proc freq data=have;
    tables date*code / nopercent norow nocol;
run;

Re: dynamic code

ballardw — Fri, 15 May 2020 14:14:13 GMT

@rohithverma wrote:
Sorry but i am unable to generate like below

DATE G2345 TOP AUTO TOTAL
10-MAY-2020 1 0 1 2
11-MAY-2020 1 2 0 3
GRANDTOTAL 2 2 1 5

Show the exact code that you submitted. Copy from the LOG the entire proc step and any messages that appear in the log. Paste the text into a code box opened on the forum with the </> icon.

Re: dynamic code

biopharma — Fri, 15 May 2020 16:47:43 GMT

It looks like you are looking for a cross tab. However, prior to that you will need to prep your data for it with an intermediary step. If you just want an output, you can stop with the FREQ. If you want the output in a dataset, prep one more time before you TRANSPOSE.

data almostHave;
input DATE date11. CODE $ ;
format date date11.;
cards;
10-MAY-2020         G2345 
10-MAY-2020          AUTO 
11-MAY-2020         G2345  
11-MAY-2020          TOP  
11-MAY-2020          TOP  
;
run;

data have ;
   set almostHave ;
   datec = put(date,date11.) ;
   output ;
   datec = "GRANDTOTAL" ;
   output ;
   drop date ;
run ;

ods output crosstabfreqs = ctab ;
proc freq data = have ;
   tables datec * code / sparse ;
run ;
ods output close ;

data wannabe ;
   set ctab ;
   where _type_ in ("10","11") ;
   if code = " " then code = "TOTAL" ;
run ;

proc transpose 
     data = wannabe
     out = want (drop=_:)
     ;
    var frequency ;
    by datec ;
    id code ;
run ;