Data step : Split data sets according to month

[Haikuo]

Data borrowed from Art's post. Hash of Hash (HOH) Method credited to  Richard DeVenezia, who was the first in the world to have it unveiled.

data date;

  informat rx_date mmddyy8.;

  input rx_date no value;

  format rx_date date8.;

  cards;

01012010     1 1

01012010     2 2

02012010     1 3

02012010     3 4

03012010     4 3

03012010     5 2

04012010     4 1

04012010     5 0

04012010     6 7

05012010     1 6

05012010     5 5

06012010     1 4

06012010     5 3

06012010     7 2

06012010     8 1

06012010     9 2

07012010     5 3

07012010     7 4

08012010     9 5

09012010     4 6

10012010     10 7

10012010     11 8

11012010     1 9

11012010     12 10

12012010     7 33

01012011     1 32

02012011     11 47

03012011     12 24

;

data temp;

  set date;

  month=catx('_',put(rx_date,monname3.),year(rx_date));

run;

data _null_ ;

  declare hash hoh (ordered: 'a');

  declare hiter hih ('hoh');

  hoh.definekey  ('month');

  hoh.definedata('month', 'hh');

  hoh.definedone ();

declare hash hh();

  do _n_=1 by 1 until (done);

     set temp end=done;

if hoh.find() ne 0 then do;

hh=_new_ hash (ordered:'a');

         hh.definekey('_n_');

hh.definedata ('rx_date', 'no', 'value');

hh.definedone();

hoh.replace();

end;

hh.replace();

end;

do rc=hih.next() by 0 while (rc=0);

hh.output(dataset: month);

rc=hih.next();

end;

stop;

run;

Regards,

Haikuo

[skallamp]

Hi Art,

I have attached the input file in excel. Please refer "dateoffill" field which is in yyyymmdd format. I am still not able to split this as I am getting the error as type mismatch...

Could you please help me to figure this out?

[Haikuo · Accepted solution]

Here is a version works on your real data based on Art's code, you may need to change libref location though.

libname myxl 'h:\data.xls';

data temp;

set myxl.'sheet1$'n;

format dateoffill date9.;

dateoffill=input(put(dateoffill,8.),yymmdd8.);

month=catx('_',put(dateoffill,monname3.),year(dateoffill));

run;

proc sort data=temp;

by month;

run;

proc sql noprint;

  select quote(name)

    into :vars separated by ','

      from dictionary.columns

        where libname="WORK" and

              memname="TEMP" and

              name ne "month"

  ;

quit;

data _null_ ;

  dcl hash hh   () ;

  hh.definekey  ('k') ;

  hh.definedata (&vars.) ;

  hh.definedone () ;

  do k = 1 by 1 until ( last.month ) ;

    set temp ;

    by month;

    hh.add () ;

  end ;

  hh.output (dataset: month) ;

run ;

Regards,

Haikuo

[skallamp]

Hi Hai.kuo,

I have received another input file where date of fill is on 01Jan2011 format. What are the changes I have to make in this code if I get different date format? I am using EG to import the file and making the date field as number. Is this correct?

Thanks,

+Sathyan

[Haikuo]

Hi Sathyan,

All you need to modify is the informat that you used to readin this variable:

data temp;

set myxl.'sheet1$'n;

format dateoffill date9.;

dateoffill=input(put(dateoffill,$9.),date9.);

month=catx('_',put(dateoffill,monname3.),year(dateoffill));

run;

Regards,

Haikuo

[skallamp]

Thanks Haikuo.

What if the date is in 2011-01-31 format?

[Haikuo]

Then try:

dateoffill=input(put(dateoffill,$10.),yymmdd10.);

just out of curiousity, what is going on with your data? Is it really messy like this, switching formats all the time?

[Linlin]

You can put the variables you want in a macro variable.

In the example below, you want variables  a,b,c, d, but don’t  want variable ID

data have ;

input id$ a b c d;

cards;

aa 1 3 4 5 6

;

proc sql noprint;

  select quote(trim(name)) into :wanted separated by ','

    from dictionary.columns

        where libname='WORK' and memname='HAVE' and upcase(name) ne 'ID';

quit;

• hh.definedata(&wanted);

note: libname and memname have to be in capital letters.

[Haikuo]

if add all of the variables, you can also do: h.definedate(all:'y');

Haikuo

[Prashant_Ph]

Sir the quest is how to split the date set in to multipal data set by month wise? 

Ex:- I have a data set XYZ it have a observation called 'Date'  and Date varibale have a daly sales or etc observation in MMDDYY formate and i want to split the data according to the month wise like JAN FEB MAR till DEC.

i got the point of using data set names like

Data Jan Feb etc;

;???????

run; 

for forther detailes i have atteched file.

Thank You