Hi I just have a very simple question for a code I used before but just wouldn't work now. Basically I just wanted to use %do year %to year to stack up a series of data sets from multiple years. The example I'm using below is just for 3 years. For some reasons the following code isn't working      data combined;          set %do year = 2009 %to 2011;              d&year          %end;;      run; and would give me error messages "%DO statement is not valid in open code" and pointed to d&year as - Syntax error, expecting one of the following: a name, a quoted string, (, -, :,;, END, INDSNAME, KEY, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_. - The symbol is not recognized and will be ignored. Same data are below. Thanks a great deal. data d2009; input id; datalines; 1 2 4 ; data d2010; input id; datalines; 3 5 ; data d2011; input id; datalines; 3 6 ; data combined;     set %do year = 2009 %to 2011;             d&year     %end;; run; ... View more

Hello, I'd like compare two dates (say start date and end date for program admission and discharge, moving in and out of a city) with another two dates (usually by calendar year from Jan 1 to Dec 31, or fiscal year from April 1 of one year to March 31 of the next year). I'd like to output records that meet the criteria for each year so that I can know if they are active in a given year. Here is an example and in this particular cases, the comparison range is April 1 to March 31. So a person's record should be outputted for each year he or she is active between April 1 of and March 31. The end date can be missing (though I could set the missing as the current date) and a person could have multiple entries and exits. I've the following code that is doing the job, but I am hoping to simplify the code by using macro or write different code. How do I start? I learned from this forum to use the function year(intnx("YEAR.4",sdate,0)) to select fiscal year. I know there are numerous date functions but I couldn't find examples similar to what I wanted to do. Many thanks. data test; format id best. startdate  enddate yymmdd10.; input id @3 startdate yymmdd10. @14 enddate yymmdd10. @25 amount; datalines; 1 2003-04-01 2005-01-01 3 1 2007-06-01 2009-01-01 4 2 2004-01-01 2009-12-01 2 3 2003-01-01 2005-01-02 9 3 2007-01-01            4 4 2006-06-01 2009-01-01 1 5 2007-07-01 2009-01-01 0 ; run; data  yr2002 yr2003 yr2004 yr2005 yr2006 yr2007 yr2008 yr2009 yr2010;   set test;     if startdate <= '31Mar2003'd and (enddate >= '1Apr2002'd or enddate = .) then output yr2002;     if startdate <= '31Mar2004'd and (enddate >= '1Apr2003'd or enddate = .) then output yr2003;     if startdate <= '31Mar2005'd and (enddate >= '1Apr2004'd or enddate = .) then output yr2004;     if startdate <= '31Mar2006'd and (enddate >= '1Apr2005'd or enddate = .) then output yr2005;     if startdate <= '31Mar2006'd and (enddate >= '1Apr2005'd or enddate = .) then output yr2005;     if startdate <= '31Mar2007'd and (enddate >= '1Apr2006'd or enddate = .) then output yr2006;     if startdate <= '31Mar2008'd and (enddate >= '1Apr2007'd or enddate = .) then output yr2007; run; Results for selected years are as follows: yr2002 id startdate    enddate amount 3 2003-01-01 2005-01-02 9 yr2003 id startdate    enddate   amount 1 2003-04-01 2005-01-01 3 2 2004-01-01 2009-12-01 2 3 2003-01-01 2005-01-02 9 yr2007 id startdate   enddate     amount 1 2007-06-01 2009-01-01 4 2 2004-01-01 2009-12-01 2 3 2007-01-01 .                4 4 2006-06-01 2009-01-01 1 5 2007-07-01 2009-01-01 0 ... View more

I've read warning about using options of NODUP and NODUPKEY in Proc Sort. That is, both compare the previous observation written to the output data set, so if the observations that you want eliminated are not adjacent in the data set after the sort, they will not be eliminated. I can understand it being a problem with NODUP, but why it's also a problem with NOUPKEY? Is it true that with NODUPKEY, SAS sorts the data by the variables following BY (say by ID, NUM) and eliminates any duplicates after the first occurrence of the cases. If so, I shouldn't have duplicate cases by ID and NUM, right? Here is an example. *Records for ID#2 not adjacent and it's sex values are not the same, but I care only about duplicates by ID and NUM; data aa; input id sex $ NUM AMOUNT; datalines; 1 F 3 40 1 F 4 40 2 M 3 20 3 M 3 40 3 M 3 40 2 F 3 20 ; proc print data=aa noobs; run; proc sort data=aa out=bb nodupkey; by id num; run; proc print data=bb noobs; run; /* 1 F 3 40 1 F 4 40 2 M 3 20 3 M 3 40 */ proc sort data=aa out=cc nodup; by id; run; proc print data=cc noobs; run; /* 1 F 3 40 1 F 4 40 2 M 3 20 3 M 3 40 2 F 3 20 */ Thanks very much. I just wanted to be sure, as I prefer not to use PROC SQL and DISTINCT do to it. ***** BUT while I'm on it, I also have a question about SQL's create table name being the same as the source data name (as I just wanted to overwrite it), such as the following code. SAS would give the following waning, Can I ignore it?       WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. proc sql; create table aa as select distinct id, num from aa; quit; proc print data=aa noobs; run; Thanks, ... View more

I know I can use INPUT to convert a character variable to a date variable. But what if one of the date components is missing or out of range. Here is an example with some of the wrong day component value. Is there an easy way or a function to tell SAS to replace the out of range or missing as say day 1 or day 31 or any day? Thanks. data aa; input id date $; datalines; 1  20110304 2  20090405 3  20100399 4  20100300 5  201004 ; run; data bb; set aa; format newdate yymmdd10.; newdate=input(date, yymmdd10.); proc print noobs; run; id   date        newdate     (Ideal output) 1 20110304  2011-03-04 2 20090405  2009-04-05 3 20100399    .                2010-03-01 (or 2010-03-15, or 2010-03-31) 4 20100300    .                2010-03-01 4 201004     1920-10-04    2010-04-01 ... View more

In PROC TRANSPOSE, 1. can I ask SAS to sort the order of the variables based on the value of the transpose variable when it has a number component (e.g. yr2003 yr2004 yr2005, etc.), instead of based on the first encounter of the values? 2. when transposed, can those values be transposed as different values (say 1) instead of the actual values (e.g. year value 2003, 2004, etc.) The data example and the specific questions are as the following. data aa; input id $ year; datalines;    a1         2005    a1         2006    a1         2008    a2         2004    a2         2005    a2         2007    a2         2008    a3         2004    a3         2005    a3         2006    a3         2007    a3         2008    a3         2009    a4         2003    a4         2004    a4         2005    a4         2006    a4         2007    a4         2008    a4         2009    a5         2003    a5         2004    a5         2005    a5         2006 ; proc sort data =aa; by id year; run; proc transpose data=aa     out = bb (drop=_Name_ )    prefix = yr;     id year;     by id;     var year;     idlabel year;     run; proc print; run; The output would look like this: id    yr2005    yr2006    yr2008    yr2004    yr2007    yr2009    yr2003 a1     2005      2006      2008         .               .              .         . a2     2005         .          2008      2004      2007              .        . a3     2005      2006      2008      2004       2007        2009         . a4     2005      2006      2008      2004       2007        2009      2003 a5     2005      2006         .          2004         .              .          2003 Questions. 1.   Ultimately I'd like to have the year variables read across in ascending order from small to large 1a. Can I do it in transpose? 1b. If I have to use retain to reorder variables, any easy way to do it without having to have list all year variables? I may have > > 80 years span, so it's 80 year variables;      data want;          retain id yr2003 yr2004 yr2005 yr2006 yr2007 yr2008 yr2009;          set  bb;      run; 2. Is it possible to ask SAS in PROC TRANSPOSE to transpose the year value as 1, not as the actual year value? (I know I could use array to assign all variables for values >0 as 1. Just curious if I could do in one step in proc transpose id    yr2005 yr2006  yr2008  yr2004  yr2007 yr2009 yr2003 a1     1         1          1           .            .          .         . a2     1         .           1          1            1          .        . etc. Many thanks. ... View more