dynamic code

[rohithverma]

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

[PeterClemmensen]

Is this for reporting purposes?

[rohithverma]

yes ,automaticallty the output must be creatyed

[PeterClemmensen]

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;

[rohithverma]

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

[PeterClemmensen]

Try this

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

[RichardDeVen]

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;

[rohithverma]

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

[yabwon]

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

Bart

[ballardw]

---

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

[biopharma]

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 ;

[yabwon]

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

[novinosrin]

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;