BookmarkSubscribeRSS Feed
ChrisTurtle
Calcite | Level 5

After some guidance as to how best to get started.  I will have a dataset similar to below where I will have approximately 200 date fields and 100 IDs:

 

IDdate_idanother_date_idyet_another_date_id
101/01/201902/01/201905/01/2019
202/01/201905/01/201906/01/2019

 

I am wanting to know if there is a simpler way to do logical calculations.

e.g. if yet_another_date_id > all other date_ids without typing them all out?

4 REPLIES 4
Reeza
Super User

Is there any pattern to the names? If so, this is easier. If not, you can create a list of the variable names if you include all date variables. 

 

So it depends....

 

if another_date > max(of date_id--yet_another_date_id) then flag=1;

The question is how to define that list after the OF.

 

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

 


@ChrisTurtle wrote:

After some guidance as to how best to get started.  I will have a dataset similar to below where I will have approximately 200 date fields and 100 IDs:

 

ID date_id another_date_id yet_another_date_id
1 01/01/2019 02/01/2019 05/01/2019
2 02/01/2019 05/01/2019 06/01/2019

 

I am wanting to know if there is a simpler way to do logical calculations.

e.g. if yet_another_date_id > all other date_ids without typing them all out?


 

ChrisTurtle
Calcite | Level 5
Thank you I will look at the blog post as there will be no pattern to the names I'm afraid
unison
Lapis Lazuli | Level 10

I'd approach in this way. This assumes all date columns are named with suffix "date_id":

data have;
input id (date_id another_date_id comparison_date_id) (:date9.);
format date_id another_date_id comparison_date_id mmddyy10.;
datalines;
1 01apr2020 02apr2020 03apr2020
2 01mar2020 15jan2020 10sep2020
3 01apr2020 02apr2020 03apr2020
4 01apr2020 02apr2020 31mar2020
;
run;

*Get date cols into a macro variable for later array reference;
proc sql noprint;
	select 
		name into :datecols 
		separated by " " 
	from dictionary.columns 
	where 
	libname='WORK' and 
	memname='HAVE' and
	upcase(name) like '%DATE_ID' and
	upcase(name) <> 'COMPARISON_DATE_ID'	
	;
quit;

data want;
	set have;
	flag=0;
	array dates &datecols;
	do over dates; /*Define array to search over*/
		if comparison_date_id > dates then do; /*do comparison*/
			flag=1; /*if TRUE then flag*/
			leave; /*leave loop on first TRUE*/
		end;
	end;
run;
-unison
Kurt_Bremser
Super User

Once again, the advantage of a long dataset format comes into play:

proc transpose data=have out=long name=datename;
by id;
var _numeric_;
run;

data want;
flag = 1;
do until (last.id);
  merge
    long (
      rename=(col1=_ref)
      where=(datename = "yet_another_date_id")
    )
    long (where=(datename ne "yet_another_date_id"))
  ;
  by id;
  if _ref < col1 then flag = 0;
end;
do until (last.id);
  set long;
  by id;
  if flag then output;
end;
drop _:;
run;

Now you need only write one "variable name".

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 888 views
  • 1 like
  • 4 in conversation