## IF/Then Statements

Solved
Occasional Contributor
Posts: 6

# IF/Then Statements

Hi,

I'm not sure if this is the right community to post in, but this is my issue. I have a dataset with millions of rows set up like this:

Record NumberDOS
1

1/1/2006

26/5/2007
11/24/2009
37/1/2011
45/22/2012
38/14/2010

What I am trying to make is a data set that looks like this (with a first DOS and a binary for if they had a date of service in each year):

Record NumberFirst DOS20062007200820092010201120122013
11/1/200610010000
26/5/200701000000
38/14/201000001100
45/22/201200000010

I have no idea how to get from A to B. I'm very new to SAS and any help would be much appreciated!

Thank you

Accepted Solutions
Solution
‎12-17-2014 12:09 PM
Super Contributor
Posts: 578

## Re: IF/Then Statements

I think this might be passed to the underlying database to do the work:

proc sql;

create table want as

select

Record_Number,

Min(dos) as First_DOS,

max(case when dos>='01jan2006:0:0'dt and dos < '01Jan2007:0:0'dt  then 1 else 0 end) as DOS_2006,

max(case when dos>='01jan2007:0:0'dt and dos < '01Jan2008:0:0'dt  then 1 else 0 end) as DOS_2007

from

have

group by record_number;

quit;

All Replies
Super User
Posts: 9,599

## Re: IF/Then Statements

Hi,

Well you have two parts to your request there.  Firstly getting a transposed list of years, the second part being getting the first DOS date.  See the steps below:

data have;
record_num=1; dos='01JAN2006'd; output;
record_num=2; dos='06MAY2006'd; output;
record_num=1; dos='24JAN2009'd; output;
run;

data inter;  /* Assign a text year */
set have;
length year \$4.;
year=put(year(dos),4.);
flag=1;
run;

proc sort data=inter nodupkey;  /* Get rid of duplicates as we only need one per rec-num */
by record_num year;
run;
proc transpose data=inter out=t_want;  /* Transpose the data to get yearly values */
by record_num;
var flag;
id year;
idlabel year;
run;

proc sql;   /* Merge on the minimum date */
create table WANT (drop=_name_) as
select  A.*,
B.MIN_DOS format=date9.
from    T_WANT A
left join (select distinct RECORD_NUM,MIN(DOS)as MIN_DOS from HAVE group by RECORD_NUM) B
on      A.RECORD_NUM=B.RECORD_NUM;
quit;

Occasional Contributor
Posts: 6

## Re: IF/Then Statements

Thanks- I can't do the first step though. The data already exists through an ODBC connection and is 65+ million rows, so I'm not sure how to go through and reformat the dates. I tried the second step without doing the first, just in case because it is set up like 25JAN2014 (I misspoke before) but also has 0:00:0000:00 included in the date, so I'm not sure how to complete the first step.

Posts: 3,852

## Re: IF/Then Statements

Look up the DATEPART function.

Posts: 1,270

## Re: IF/Then Statements

data have;

input RecordNumber DOS :mmddyy10.;

format DOS mmddyy10.;

datalines;

1 1/1/2006

2 6/5/2007

1 1/24/2009

3 7/1/2011

4 5/22/2012

3 8/14/2010

;

data want(drop=i);

set have;

array yr{*} y2006-y2013;

do i=2006 to 2013;

if year(dos)=i then yr{i-2005}=1;

else yr{i-2005}=0;

end;

run;

PROC Star
Posts: 8,163

## Re: IF/Then Statements

My suggestion is very similar to stat's:

data have;

informat record_number 8.;

informat dos anydtdtm.;

infile cards dlm=',';

input record_number dos;

cards;

1,01JAN2006 14:30:08

2,05JUN2007 14:30:08

1,24JAN2009 14:30:08

3,01JUL2011 14:30:08

4,22MAY2012 14:30:08

3,22AUG2010 14:30:08

;

proc sort data=have out=want;

by record_number descending dos;

run;

data want;

format First_DOS mmddyy10.;

set want (rename=(dos=First_DOS));

by record_number;

array years(2006:2012) _2006-_2012;

retain years;

if first.record_number then do _n_=2006 to 2012;

years(_n_)=0;

end;

years(year(datepart(First_DOS)))=1;

First_DOS=datepart(First_DOS);

if last.record_number then output;;

run;

Solution
‎12-17-2014 12:09 PM
Super Contributor
Posts: 578

## Re: IF/Then Statements

I think this might be passed to the underlying database to do the work:

proc sql;

create table want as

select

Record_Number,

Min(dos) as First_DOS,

max(case when dos>='01jan2006:0:0'dt and dos < '01Jan2007:0:0'dt  then 1 else 0 end) as DOS_2006,

max(case when dos>='01jan2007:0:0'dt and dos < '01Jan2008:0:0'dt  then 1 else 0 end) as DOS_2007

from

have

group by record_number;

quit;

Occasional Contributor
Posts: 6

## Re: IF/Then Statements

Thank you SO much! This worked amazingly well - I seriously appreciate it so much.

Occasional Contributor
Posts: 6

## Re: IF/Then Statements

Got it to work! Thank you everyone for your help- I'd be lost otherwise! I really appreciate all your input!

Super User
Posts: 10,768

## Re: IF/Then Statements

```data have;
input RecordNumber DOS :mmddyy10.;
format DOS mmddyy10.;
datalines;
1 1/1/2006
2 6/5/2007
1 1/24/2009
3 7/1/2011
4 5/22/2012
3 8/14/2010
;
run;

proc sort data=have; by RecordNumber DOS;run;
data have;
set have;
by  RecordNumber;
retain first_dos var 1;
if first.RecordNumber then first_dos=DOS;
year=year(DOS);
format  first_dos date9.;
run;
proc transpose data=have out=temp(drop=_name_) prefix=_;
by RecordNumber first_dos;
id year;
var var;
run;
proc stdize data=temp out=want missing=0 reponly;run;

```

Xia Keshan

🔒 This topic is solved and locked.