07-07-2011 04:57 PM
I am extracting data from a table like this one below and having difficulties with first.[variable] , last.[variable] processing. I need to get first and last observations for each ID&Date together. For instance, for ID=100 I should receive obs 1, 3 as 'first' and obs 2, 6 as 'last'. For ID=101 it should be obs 7, 9 as 'first' and 8, 10 as 'last'. Instead my program reads only the ID variable and returns obs 1 as 'first' and obs 6 as 'last' for ID=100, then obs 7 as 'first' and obs 10 as 'last' for ID=101. Example of my code for 'last':
by ID Date;
if last.ID and last.Date then output;
What am I doing wrong? How to get my first and last observations based on two criteria together? Thank you.
07-07-2011 05:56 PM
Some self-diagnosis output can be generated by adding this statement to a DATA step, so you can see in your SASLOG the SAS-generated temporary variables for FIRST.<byvar_> and/or LAST.<byvar_>. This information will help diagnose the program's execution more visually.
Basically, you are only going to "OUTPUT" the last-occurrence condition of both of your BY statement variables. So, then, if you have a requirement to generate observations for other "OR" conditions, you will need to code them in your IF (<expression1>) OR (<expression2) THEN <action>; statement.
Suggested Google advanced search argument, this topic / post:
by group processing site:sas.com
07-07-2011 05:56 PM
Of course your data have to be sorted by id and date.
If it is, then your datastep is simply trying for the wrong things. As I understand what you want, you simply want the first and last records for a given ID on any date.
If so, then you only need:
by ID Date;
if first.date or last.Date then output;
Of course, if you only want the last record on a date then replace the if with:
if last.Date then output;
08-30-2017 09:09 AM
I tried using the same code but it didnt work. i need to get for each usubjid i need the first ECSTDTC(start date) and last ECENDTC (end date) so basically one record per usubjid.
proc sort data=ec out=ec7; by usubjid ECSTDTC; run;
by usubjid ECSTDTC ECENDTC;
if First.ECSTDTC and last.ECENDTC then output;
03-06-2012 09:15 PM
Hi can anyone explain me that what is logic behind this sentence in SAS.
"IF NOT FIRST.CPNP AND LAST.CPNP AND PLANT='USM' THEN DELETE;"
I am new to SAS so, please let me know that this query will delete duplicate rows from the data set or something else??
03-06-2012 09:23 PM
This will delete the last CPNP record if it is not the first record of the CPNP group and where plant=USM only.
It will not delete all duplicates if your CPNP group has more than two records.
12-15-2016 03:36 PM
I would like to get the maximum date value from VCI_setup-date
but in the if condition I want to give all the three variables name, group , chart type
All these three variables by together have different dates and I want only the maximum.
by name group chart_type descending VCI_SETUP_Date;
Thanks in advance!
Need further help from the community? Please ask a new question.