SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
DavidLie
Obsidian | Level 7

Hi SAS Communities,

 

I have two datasets here:

(1) Have1: Showing all the date ranges. Example:

ID Date_From Date_To
1 21 Feb 2000 14 Mar 2000
1 23 Jun 2000 17 Jul 2000
1 18 Oct 2000 8 Nov 2000
.... ... ...
2 29 Sept 2000 01 Jan 2001
... ... ...

 

(2) Have2: Listings of all dates. Example:

Date Price
01 Jan 2000 x
02 Jan 2000 x
03 Jan 2000 x
.... ...
31 Dec 2020 x

 

I would like to have a dataset that have the following:

Date Price ID1 ID2
01 Jan 2000 x 0 0
02 Jan 2000 x 0 0
03 Jan 2000 x 0 0
.... ... ... ...
21 Feb 2000 x 1 0
22 Feb 2000 x 1 0
... ... ... ...
14 Mar 2000 x 1 0
15 Mar 2000 x 0 0
16 Mar 2000 x 0 0
... ... ... ...
31 Dec 2020 x 0 0

 

In short, the dataset above has the binary code such that the date that falls within the range will be 1, otherwise 0.

 

Best,

David

4 REPLIES 4
andreas_lds
Jade | Level 19

Please post data in usable form.

Shmuel
Garnet | Level 18

In 1st table you posted are IDs 1, 2 - are there more?

Are IDs sequential (1 2 3 ...) or any numeric values?

Does ID1 of table-2 relate to ID=1 of table-1, ID2 to id=2 etc? Or did you mean something else?

PeterClemmensen
Tourmaline | Level 20

See if you can use this as a template.

 

Feel free to ask 🙂

 

/* Example Data */
data have;
input ID (Date_From Date_To)(:date9.);
format Date: date9.;
datalines;
1 21Feb2000 14Mar2000
1 23Jun2000 17Jul2000
1 18Oct2000 08Nov2000
2 11Mar2000 14Mar2000
2 13Jun2000 17Aug2000
2 11Oct2000 09Nov2000
3 21Feb2000 14Mar2000
3 23Jun2000 17Jul2000
3 18Oct2000 08Nov2000
;

data price;
   do date = "01jan2000"d to "31dec2000"d;
      price = ceil(rand('uniform')*100);
      output;
   end;
   format date date9.;
run;

/* Find Largest ID */
proc sql noprint;
   select max(ID) into :m separated by ' '
   from have;
run;

%put &=m.;

/* Create wanted data set */
data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "have", multidata : "Y");
      h.definekey("ID");
      h.definedata(all : "Y");
      h.definedone();
   end;

   set price;
   array i id1-id&m.;
   if 0 then set have;

   do over i;
      do while (h.do_over(key : _I_) = 0);
         if Date_From le date le Date_To then i = 1;
      end;
   end;
run;
PhilC
Rhodochrosite | Level 12

In my opinion, this is a PROC TRANSPOSE question.  I'm using an indexed dataset, MERGE could be used.  I also believe hash table solution is a bit overkill:

 

data have;
input ID (Date_From Date_To)(:date9.);
format Date: date9.;
datalines;
1 21Feb2000 14Mar2000
1 23Jun2000 17Jul2000
1 18Oct2000 08Nov2000
2 11Mar2000 14Mar2000
2 13Jun2000 17Aug2000
2 11Oct2000 09Nov2000
3 21Feb2000 14Mar2000
3 23Jun2000 17Jul2000
3 18Oct2000 08Nov2000
;
data price (Index=(date /unique));
   do date = "01jan2000"d to "31dec2000"d;
      price = ceil(rand('uniform')*100);
      output;
   end;
   format date date9.;
run;
data temp;
  set have;
    drop Date_From	Date_To;
  do date=Date_From to Date_To;
    truth=1; /*a dummy variable*/
    set price key=date /unique;
    output;
  end;
proc sort;
	BY date price;
run;
PROC TRANSPOSE DATA=temp
	OUT=WORK.WANT(drop=_NAME_)
	PREFIX=ID
;
	BY date price;
	ID ID;
	VAR truth;
RUN;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 869 views
  • 0 likes
  • 5 in conversation