topic Re: Data step : Split data sets according to month in SAS Programming

Data step : Split data sets according to month

skallamp — Wed, 28 Mar 2012 14:54:23 GMT

Hi,

Could you please help me to split the data in to multiple data sets based on the Year and month.

For e.g My input data contains two year data, I want to split it in to 24 small data sets based on the month and year.

I used the below query to get month and year.

data

set claim_file

informat x_date date8.

format x_date date8.

M = month(x_date)

Y = year(x_date)

run

Out put should have a data set name like Jan_2011, Feb_2011 etc..

Please help.

Thanks.

Data step : Split data sets according to month

Howles — Wed, 28 Mar 2012 22:21:12 GMT

See http://www.sascommunity.org/wiki/Split_Data_into_Subsets

Be sure to notice the advisory against doing this.

skallamp wrote:

Hi,

Could you please help me to split the data in to multiple data sets based on the Year and month.

For e.g My input data contains two year data, I want to split it in to 24 small data sets based on the month and year.

I used the below query to get month and year.

data

set claim_file

informat x_date date8.

format x_date date8.

M = month(x_date)

Y = year(x_date)

run

Out put should have a data set name like Jan_2011, Feb_2011 etc..

Please help.

Thanks.

Data step : Split data sets according to month

Ksharp — Thu, 29 Mar 2012 06:11:59 GMT

data have;
 input month : yymmn. no;
 format month yymmn.;
 cards;
201001     1
201001     2
201002     1
201002     3
201003     4
201003     5
201004     4
201004     5
201004     6
201005     1
201005     5
201006     1
201006     5
201006     7
201006     8
201006     9
201007     5
201007     7
201008     9
201009     4
201010     10
201010     11
201011     1
201011     12
201012     7
201101     1
201102     11
201103     12
;
run;

proc sort data=have;by month;run;
data _null_ ;
  dcl hash hh   () ;
  hh.definekey  ('k') ;
  hh.definedata ('month','no') ;
  hh.definedone () ;
  do k = 1 by 1 until ( last.month ) ;
    set have ;
    by month groupformat ;
    format month monyy.;
    hh.add () ;
  end ;
  hh.output (dataset: put(month,monyy5.)) ;
run ;

Ksharp

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 11:08:21 GMT

Thanks for the thoughts. I am getting the below error while applying it to my data.

15 data _null_ ;

16 dcl hash hh () ;

17 hh.definekey ('k') ;

18 hh.definedata ('RX_DATE','no') ;

19 hh.definedone () ;

20 do k = 1 by 1 until ( last.RX_DATE ) ;

21 set date ;

22 by rx_date groupformat ;

23 format rx_date monyy.;

24 hh.add () ;

25 end ;

26 hh.output(dataset: put(rx_date,monyy5.)) ;

27 run ;

ERROR: Undeclared data symbol no for hash object at line 19 column 3.

ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 12:39:18 GMT

If I have more variables, how do I put everything with out typing it one by one.

Here hh.definedata('rx_date') is giving me only one variable. I need all of them which is present in my data set.

Data step : Split data sets according to month

Linlin — Thu, 29 Mar 2012 13:21:00 GMT

add all your variables:

hh.definedata('rx_date','your_var1','your_var2')

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 13:55:48 GMT

My Variables are keep changing from one work to another.

Is there a way I can add all variables using a simple statement? somethin like *..

Data step : Split data sets according to month

Haikuo — Thu, 29 Mar 2012 14:04:28 GMT

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

Haikuo

Re: Data step : Split data sets according to month

art297 — Thu, 29 Mar 2012 14:11:43 GMT

I just learned something new from Hai.kuo but, since I created a new field in my suggested code, used proc sql to build a list of all the variables EXCEPT the one I created. While a I agree with Howles that you probably DON'T want to do this, would something like the following suffice?:

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;

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 ;

Re: Data step : Split data sets according to month

Linlin — Thu, 29 Mar 2012 14:15:37 GMT

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.

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 14:26:29 GMT

Hi Art,

Its giving me the below error.

36 dcl hash hh () ;

37 hh.definekey ('k') ;

38 hh.definedata (&vars.) ;

386

200

WARNING: Apparent symbolic reference VARS not resolved.

ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

39 hh.definedone () ;

ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.

NOTE: The SAS System stopped processing this step because of errors.

Re: Data step : Split data sets according to month

Linlin — Thu, 29 Mar 2012 14:29:19 GMT

Hi Art,

I think ' memname="NEED" and' in your code should be ' memname="TEMP" and'

Thank you! - Linlin

Updated after Art's comments

Data step : Split data sets according to month

art297 — Thu, 29 Mar 2012 14:33:09 GMT

Like Linlin mentioned, there was an error in my sql statement. However, it should not have been "date" but, rather, "temp". i.e.,

proc sql noprint;

select quote(name)

into :vars separated by ','

from dictionary.columns

where libname="WORK" and

memname="TEMP" and

name ne "month"

;

quit;

Since you ran my original code suggesting using "need", you should have gotten an error when you ran the proc sql code.

Art

Data step : Split data sets according to month

Linlin — Thu, 29 Mar 2012 14:43:54 GMT

Art,

Why 'temp' not 'date'? Thank you!

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 15:42:56 GMT

Hi This is what I am using...still i m getting error

data temp;

set claim_detail;

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

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="NEED" 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 ;

Data step : Split data sets according to month

Linlin — Thu, 29 Mar 2012 15:52:05 GMT

change

memname="NEED"

memname="TEMP"

Data step : Split data sets according to month

skallamp — Thu, 29 Mar 2012 15:54:04 GMT

My error is b'cse of this statement.

Temp output has blank value for month...my rx_date format is yyyymmdd format

month=catx(

),year(rx_date));

Re: Data step : Split data sets according to month

Haikuo — Thu, 29 Mar 2012 17:31:48 GMT

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

Data step : Split data sets according to month

skallamp — Fri, 30 Mar 2012 09:16:29 GMT

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?

Data step : Split data sets according to month

Haikuo — Fri, 30 Mar 2012 12:19:53 GMT

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