topic Re: how to create a dynamic datasets in SAS Programming

how to create a dynamic datasets

SRINIVAS_N — Thu, 06 Sep 2018 15:20:42 GMT

Hi All,

can any one help me

i have a datasets name called one ,and variable is year.Based on variable year i have to create a dynamic dataset

Below is the code

data one;
input year ;
cards;
2015
2015
2016
2017
2018
;
run;
proc sql;
select count(distinct year) into:cnt
from one;
quit;
proc sql;
select distinct year into :yr1-:%sysfunc(compress(yr&cnt.))
from one;
quit;
options symbolgen mprint mlogic;
%macro test;
%do i=1 %to &cnt;
data year_&i;
set one;
where year =&&yr&i;
run;
%end;
%mend test;
%test;

i am getting output as work.year1

work.year2

work.year3

work.year4

instead of year1 and year2

i want work.2015

work.2016

work.2017

pls help me

Re: how to create a dynamic datasets

RW9 — Thu, 06 Sep 2018 15:26:04 GMT

First off, its not a good idea to split same data - that is an Excel way of thinking.

You could do:

proc sort data=one out=loop nodupkey;
  by year;
run;

data _null_;
  set loop;
  call execute(cats('data year',put(year,4.),'; set one; where year=',put(year,4.),'; run;'));
run;

This will create one datastep for each unique year in dataset one.

Re: how to create a dynamic datasets

novinosrin — Thu, 06 Sep 2018 16:14:08 GMT

data one;
input year ;
cards;
2015
2015
2016
2017
2018
;
run;
proc sql;
select count(distinct year) into:cnt trimmed
from one;
quit;
proc sql;
select distinct year into :yr1- trimmed
from one;
quit;

%put &yr4;
options nosymbolgen nomprint nomlogic;
%macro test;
%do i=&yr1 %to &&yr&cnt;
data year_&i;
set one;
where year =&i;
run;
%end;
%mend test;
%test;

I prefer @RW9 's call execute approach and like he said it is not a great idea to split or sometimes I fancy using hashes but that's not really in scope for this trivial problem.

Re: how to create a dynamic datasets

SRINIVAS_N — Thu, 06 Sep 2018 16:30:55 GMT

Hi ,

Thanks for your support

i am getting output as

year2015

year2016

year2017

i want to get output as

data_2015

data_2016

data_2017

will u pls support me

Re: how to create a dynamic datasets

SRINIVAS_N — Thu, 06 Sep 2018 16:39:04 GMT

Hi ,

Thanks for your support

i am getting output as

year2015

year2016

year2017

i want to get output as

data_2015

data_2016

data_2017

will u pls support me

Re: how to create a dynamic datasets

novinosrin — Thu, 06 Sep 2018 16:46:43 GMT

All you need to do is

change

data year_&i;

data data_&i;

in the code:

%macro test;
%do i=&yr1 %to &&yr&cnt;
data year_&i; /*change here*/
set one;
where year =&i;
run;
%end;
%mend test;

Re: how to create a dynamic datasets

SRINIVAS_N — Thu, 06 Sep 2018 16:59:58 GMT

thanks yaar

one more small request

if the date is before 2018 years then show the date format as date9.

if the date is after than 2018 year then show the date format is ddmmyy10

pls suggest me yaar

Re: how to create a dynamic datasets

novinosrin — Thu, 06 Sep 2018 17:02:19 GMT

I can't see any date values in your sample

Re: how to create a dynamic datasets

SRINIVAS_N — Thu, 06 Sep 2018 17:07:28 GMT

sorry yaar i forget to mention date column in the sample code

data one;

input date year;

format date date9.;

cards;

01jan2018 2018

02jan2017 2017

17dec2016 2016

25feb2014 2014

02feb2015 2015

;

run;

this is the sample code yaar

i want a date set year wise and if the date set is before previous year then date format should be mmddyy10.

if the date is current year then date format is date9.

pls support thank for advance

Re: how to create a dynamic datasets

RW9 — Fri, 07 Sep 2018 07:56:18 GMT

So your not even going to be consistent within your own process flow? How are you going to use this data further, creating masses of macro code to try to utilise this data. It is really not a good idea to split like data up.

data _null_;
  set year;
  if year < year(today())-1 then 
    call execute(cats('data data_',put(date,mmddyy10.),'; set yourds; where year=',put(year,4.),'; run;'));
  else 
    call execute(cats('data data_',put(year,4.),'; set one; where year=',put(year,4.),'; run;'));
run;

You really are however opening yourself up to world of pain though unless this is just for export - and then your merely passing that pain onto someone else.

Re: how to create a dynamic datasets

s_lassen — Fri, 07 Sep 2018 08:03:46 GMT

IMO, the macro approach is not so bad. But I would do it differently:

%macro Year_data(year);
  data data_&year;
    set one;
    where year=&year;
    %if &year=%sysfunc(date(),year4.) %then
      format date date9.;
   %else 
      format date mmddyy10.;
      ;
  run;
%mend;

proc sql noprint;
  select distinct cats('%year_data(',year,')') into :macrocall separated by ';'
  from one;
quit;

&macrocall;