topic Re: How to fill an array in PROC SQL query within a DO loop? in SAS Programming

How to fill an array in PROC SQL query within a DO loop?

nbora — Fri, 19 Jan 2024 08:26:20 GMT

Hi,

I'm trying to fill an array in a PROC SQL query within a DO loop and at the end of the loop I'd like to feed this array into a dataset.

Here is my failing code. Any help/tip/solution is appreciated.

Cheers!

%macro testloop;

data count_array;

array monthly_count(12) _numeric_;

DO current_date = '30JUN2013'd to '31MAY2014'd by month;

PROC SQL;

SELECT

COUNT(*) into :monthly_count

FROM

<table_name>

WHERE

<date_column> = "&current_date"d

;

QUIT;

END;

drop current_date;

run;

data wd.output_table;

set count_array;

run;

%MEND;

Re: How to fill an array in PROC SQL query within a DO loop?

Kurt_Bremser — Fri, 19 Jan 2024 08:45:23 GMT

You cannot nest a procedure in a DATA step.

Please post a example of your "have" data, and what you want to get out of it.

Re: How to fill an array in PROC SQL query within a DO loop?

nbora — Fri, 19 Jan 2024 08:59:47 GMT

Hi Kurt,

thank you for your reply.

The content of the data doesn't really matter. It has a datetime column and a product id column.

I would like to have a table that contain the number of data points by the end of each month for given period (e.g. June. 2013 - May 2014).

Cheers

Re: How to fill an array in PROC SQL query within a DO loop?

Patrick — Fri, 19 Jan 2024 09:15:32 GMT

@nbora If I understand your requirement correctly then first select only rows with a date that's end of month (where clause) and then count these selected rows by date (group by / count(*) ).

PROC SQL;
/*  create table wd.output_table as*/
  SELECT <date_column>, COUNT(*) as n_rows
  FROM <table_name>
  WHERE <date_column> = intnx('month',<date_column>,0,'e')
  group by <date_column>
;
QUIT;

Re: How to fill an array in PROC SQL query within a DO loop?

Kurt_Bremser — Fri, 19 Jan 2024 10:05:50 GMT

So something like this

proc freq data=have;
format datetime dtmonyy7.;
tables datetime;
run;

Re: How to fill an array in PROC SQL query within a DO loop?

PaigeMiller — Fri, 19 Jan 2024 10:25:21 GMT

@nbora wrote:

Hi Kurt,

The content of the data doesn't really matter. It has a datetime column and a product id column.

Okay, please make up some data for us to use, that represents your problem.

Re: How to fill an array in PROC SQL query within a DO loop?

nbora — Fri, 19 Jan 2024 13:00:33 GMT

Ok. The could look like this.

data dummy_data;

input col_date col_X

datalines;

08Jun2013 X

11Jun2013 X

18Jun2013 X

30Jun2013 X

14Jul2013 X

31Jul2013 X

31Aug2013 X

30Sep2013 X

10Oct2013 X

10Nov2013 X

30Nov2013 X

10Dec2013 X

31Dec2013 X

31Jan2014 X

12Feb2014 X

28Feb2014 X

31Mar2014 X

11Apr2014 X

10Mai2014 X

22Mai2014 X

31Mai2014 X

;

And here is my desired out put:

30Jun2013 2

31Jul2013 1

31Aug2013 1

30Sep2013 1

31Oct2013 0

30Nov2013 1

31Dec2013 3

31Jan2014 1

28Feb2014 1

31Mar2014 1

30Apr2014 0

31Mai2014 1

Re: How to fill an array in PROC SQL query within a DO loop?

nbora — Fri, 19 Jan 2024 13:04:03 GMT

Hi,

Ok. The data could look like the following:

data dummy_data;
  input col_date col_X
datalines;
08Jun2013 X
11Jun2013 X
18Jun2013 X
30Jun2013 X
30Jun2013 X
14Jul2013 X
31Jul2013 X
31Aug2013 X
30Sep2013 X
10Oct2013 X
10Nov2013 X
30Nov2013 X
10Dec2013 X
31Dec2013 X
31Dec2013 X
31Dec2013 X
31Jan2014 X
12Feb2014 X
28Feb2014 X
31Mar2014 X
11Apr2014 X
10Mai2014 X
22Mai2014 X
31Mai2014 X
;

And I would like to know the number of datapoints at the last day of each month for a given period of time.

Re: How to fill an array in PROC SQL query within a DO loop?

PaigeMiller — Fri, 19 Jan 2024 13:18:48 GMT

data dummy_data;
  input col_date date9. col_X $ ;
datalines;
08Jun2013 X
11Jun2013 X
18Jun2013 X
30Jun2013 X
30Jun2013 X
14Jul2013 X
31Jul2013 X
31Aug2013 X
30Sep2013 X
10Oct2013 X
10Nov2013 X
30Nov2013 X
10Dec2013 X
31Dec2013 X
31Dec2013 X
31Dec2013 X
31Jan2014 X
12Feb2014 X
28Feb2014 X
31Mar2014 X
11Apr2014 X
10May2014 X
22May2014 X
31May2014 X
;

proc freq data=dummy_data;
    tables col_date;
    format col_date monyy.;
run;

I changed where you had written 31Mai2014 to 31May2014, this works for me where I am expecting English dates. You can leave it in your language and use 31Mai2014, I know this is possible by setting a language option somewhere, but I do not know how to do that. I'm sure someone else here knows how to do that.

Re: How to fill an array in PROC SQL query within a DO loop?

nbora — Fri, 19 Jan 2024 13:36:08 GMT

Thank you, @PaigeMiller . Sorry for the miscommunication but what I meant by "at the last day of each month" was not the total number of datapoints by the end of each month. Literally on the last day of each month. Like the following: That's why I tried to write rather a complicated macro like in my original post.

30Jun2013 2

31Jul2013 1

31Aug2013 1

30Sep2013 1

31Oct2013 0

30Nov2013 1

31Dec2013 3

31Jan2014 1

28Feb2014 1

31Mar2014 1

30Apr2014 0

31May2014 1

;

Re: How to fill an array in PROC SQL query within a DO loop?

Kurt_Bremser — Fri, 19 Jan 2024 13:38:48 GMT

Add a WHERE= dataset option:

proc freq data=have (where=(col_date = intnx('month`,col_date,0,'e')));
tables col_date;
run;

Re: How to fill an array in PROC SQL query within a DO loop?

Patrick — Sat, 20 Jan 2024 00:09:25 GMT

@nbora Re-posting the SQL I shared already earlier.

data dummy_data;
  input col_date :date9. col_X $;
  format col_date date9.;
datalines;
08Jun2013 X
11Jun2013 X
18Jun2013 X
30Jun2013 X
30Jun2013 X
14Jul2013 X
31Jul2013 X
31Aug2013 X
30Sep2013 X
10Oct2013 X
10Nov2013 X
30Nov2013 X
10Dec2013 X
31Dec2013 X
31Dec2013 X
31Dec2013 X
31Jan2014 X
12Feb2014 X
28Feb2014 X
31Mar2014 X
11Apr2014 X
10May2014 X
22May2014 X
31May2014 X
;

PROC SQL;
/*  create table wd.output_table as*/
  SELECT col_date, COUNT(*) as n_rows
  FROM dummy_data
  WHERE col_date = intnx('month',col_date,0,'e')
  group by col_date
  ;
QUIT;