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
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?
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
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
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
Thanks Haikuo.
What if the date is in 2011-01-31 format?
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?
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;
note: libname and memname have to be in capital letters.
if add all of the variables, you can also do: h.definedate(all:'y');
Haikuo
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.