topic Re: Proc freq using calculated variable in SAS Enterprise Guide

Proc freq using calculated variable

rowansdad — Wed, 29 Aug 2018 10:19:00 GMT

Hi There,

I am looking to create a PROC FREQ output to do a quick check on the number of variables for each day past a certain date.

This issue is the date field in the source table is in date time format (e.g. 07AUG2006:16:25:00).

I currently do this in PROC SQL with the following code:

Proc SQL;
Create table DateCheck as
Select datepart(DateCreated)> format date9. as date, Count(*) as Volume
From dataflow.datasource
Where datepart(DateCreated)>='01JUL2018'd
group by date;
Quit;

However, I would like to be able to do a PROC FREQ like this but it will not allow the datepart function:

proc freq data=dataflow.datasource;
tables datepart(DateCreated);
where datepart(DateCreated GE intnx('month',&month.,0,'b');
format DateCreated date9.;
run;

Is this possible?

Thanks,

Jon

Re: Proc freq using calculated variable

Kurt_Bremser — Wed, 29 Aug 2018 10:42:38 GMT

You can move the where statement into a where= dataset option, but the use of a data step function in proc freq itself does not work. In a tables statement, only existing variables can be used.

One of the reasons that proc freq outperforms SQL is this restriction.

Re: Proc freq using calculated variable

RW9 — Wed, 29 Aug 2018 10:48:54 GMT

Nope, you would need to datepart the date into a new variable first.

You do of course realise there are various mistakes in both sets of code there? An arrow in the select before format for instance, or missing end bracket in the where of the second code, not to mention a macro variable not used in the first code. Also, please use a code window - its the {i} above the post area for code in future, its very hard to read the code presented there.

data want;
  set dataflow.datasource;
  dvar=datepart(datecreated);
  format dvar date9.;
run;

proc freq data=dataflow.datasource;
  tables dvar;
  where dvar ge intnx('month',&month.,0,'b');
run;

Re: Proc freq using calculated variable

FreelanceReinh — Wed, 29 Aug 2018 15:48:27 GMT

Hi @rowansdad,

I think you need the DTDATEw. format.

Example:

data have;
input DateCreated datetime.;
cards;
30JUN2006:16:25:00
07AUG2006:16:25:00
07AUG2006:16:26:00
08AUG2006:16:25:00
;

proc freq data=have;
format DateCreated dtdate9.;
tables DateCreated;
where datepart(DateCreated) ge '01JUL2006'd;
run;

Re: Proc freq using calculated variable

ballardw — Wed, 29 Aug 2018 15:52:27 GMT

@rowansdad wrote:

Hi There,

I am looking to create a PROC FREQ output to do a quick check on the number of variables for each day past a certain date.

Not quite clear on what this means. The number of variables in a data doesn't change based on the value of a variable. Do you mean the number of variables with non-missing values?? The code looks more like you a looking for the number of records where a variable is within a stated range of values.

And there really isn't much reason to play around with extracting dateparts as a format applied to an existing value will count days based on datetime values.

data example;
  informat dt datetime18.;
  input dt;
  format dt datetime18.;
datalines;
01MAR2018:01:02:03
01MAR2018:01:03:03
01MAR2018:01:04:03
01MAR2018:01:05:03
01MAR2018:01:06:03
03MAR2018:01:03:03
03MAR2018:01:04:03
03MAR2018:01:05:03
04MAR2018:01:06:03
;
run;

proc freq data=example;
   format dt datetime9.;
   tables dt;
run;

Re: Proc freq using calculated variable

rowansdad — Thu, 30 Aug 2018 08:44:34 GMT

Thanks, that's worked great.