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

[nbora]

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;

[Kurt_Bremser]

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.

[nbora]

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

[Patrick]

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

[Kurt_Bremser]

So something like this

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

?

[PaigeMiller]

---

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

[nbora]

Ok. The could look like this.

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

[nbora]

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.

[PaigeMiller]

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.

[nbora]

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

;

[Kurt_Bremser]

Add a WHERE= dataset option:

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

[Patrick]

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