topic Re: First. And last . in SAS Programming

First. And last .

Akshaya_1397 — Fri, 30 Jun 2023 09:56:09 GMT

Hi,

I have a datset final in which there are columns like

Id,name,function,status,reason, department and has values

123,flowers-gift,nw,onboard,blank SLS,human resource

123,flowers-gifts,nw,onboard,missing country,Human resource

123,onboard gift,nw,onboard,missing country,

I need to take unique rows so I performed first. In the dataset

Data new;

Set final;

If first.id and first.name and first.department then output;

Run;

The output I'm getting is only the first row that is with the reason blank SSL but I also need the second row with missing country.

Could anyone please help me on this

Re: First. And last .

PaigeMiller — Fri, 30 Jun 2023 09:44:20 GMT

I have a feeling that you are not representing the data in your SAS data set properly. Please provide the data (in this thread, and from now on in all future threads) as working SAS data step code (examples and instructions).

Re: First. And last .

Kurt_Bremser — Fri, 30 Jun 2023 09:50:20 GMT

You show six variables, but only five values in the data.

Please do ALWAYS post example data in a working DATA step with DATALINES, so we don‘t have to ask.

Re: First. And last .

Akshaya_1397 — Fri, 30 Jun 2023 09:56:57 GMT

Sorry for that.
I have now edited the it properly

Re: First. And last .

PaigeMiller — Fri, 30 Jun 2023 09:58:04 GMT

@Akshaya_1397 wrote:
Sorry for that.
I have now edited the it properly

This is not the format we requested.

first dot in base sas

Akshaya_1397 — Fri, 30 Jun 2023 10:49:25 GMT

i have dataset name merged file as shown below:

i wrote a code to obtain unique rows:

DATA fin;
set Merged_file;
by ID dep status reason;
if first.id and first.dep and first.status and first.reason then output;
run;

the output which im getiing is only the first row.but the output im expecting is the first two rows as the reason is different

can anyone please help me on this

Re: first dot in base sas

PaigeMiller — Fri, 30 Jun 2023 11:11:32 GMT

Please provide the data in the requested form (we provided a link with examples and instructions). Please don't start new threads on the same topic.

Re: First. And last .

Kurt_Bremser — Fri, 30 Jun 2023 11:31:52 GMT

So you have the texts „blank SLS“ and „missing country“ in place of missing values?

I will not answer further posts until you post your data as requested.

Re: first dot in base sas

Kurt_Bremser — Fri, 30 Jun 2023 11:34:21 GMT

I merged the posts.

Re: first dot in base sas

Tom — Fri, 30 Jun 2023 12:31:32 GMT

@Akshaya_1397 wrote:

i have dataset name merged file as shown below:

i wrote a code to obtain unique rows:

DATA fin;
set Merged_file;
by ID dep status reason;
if first.id and first.dep and first.status and first.reason then output;
run;

the output which im getiing is only the first row.but the output im expecting is the first two rows as the reason is different

can anyone please help me on this

This test

if first.id and first.dep and first.status and first.reason then output;

indicates a misunderstanding of how FIRST. flags are created.

That test is the same as

if first.id then output;

because the time when all of the FIRST. flags will be true is when you start a new value of the first BY variable, in this case the ID variable.

If you want to find the distinct observations then you should just test the value of the last BY variable, in this case the REASON variable.

if first.reason then output;

Re: first dot in base sas

Akshaya_1397 — Fri, 30 Jun 2023 12:34:28 GMT

Thanks for ur reply tom.

But we have exact same reasons for other I'd too that's why I used first.id and first.reason.

Is there any other way to do it?

Re: first dot in base sas

Tom — Fri, 30 Jun 2023 12:37:14 GMT

@Akshaya_1397 wrote:

Thanks for ur reply tom.

But we have exact same reasons for other I'd too that's why I used first.id and first.reason.

Is there any other way to do it?

Do WHAT?

Are you look to get one observation per distinct values of REASON?

If so then sort by REASON.

proc sort data=have out=one_per_reason nodupkey ;
  by reason;
run;

Re: First. And last .

Tom — Fri, 30 Jun 2023 12:54:41 GMT

All of the rows in your example dataset are unique already. Please provide example data that has one or more duplicates that you would like to remove. Explain which ones should be removed (or which ones kept) and why.

data have ;
  infile cards dsd truncover ;
  input Id $ name :$20. function :$5. status :$20. reason :$20. department :$20.;
cards;
123,flowers-gift,nw,onboard,blank SLS,human resource
123,flowers-gifts,nw,onboard,missing country,Human resource
123,onboard gift,nw,onboard,missing country, 
;

Obs    Id         name         function    status     reason               department

 1     123    flowers-gift        nw       onboard    blank SLS          human resource
 2     123    flowers-gifts       nw       onboard    missing country    Human resource
 3     123    onboard gift        nw       onboard    missing country

Re: First. And last .

Akshaya_1397 — Fri, 30 Jun 2023 13:10:17 GMT

data have ;
infile cards dsd truncover ;
input Id $ name :$20. function :$5. status :$20. reason :$20. department :$20.;
cards;
123,flowers-gift,nw,onboard,blank SLS,human resource
123,flowers-gifts,nw,onboard,missing country,Human resource
123,onboard gift,nw,onboard,missing country,

124,flowers-gift,nw,onboard,blank SLS,human resource
124,flowers-gifts,nw,onboard,missing country,Human resource
124,onboard gift,nw,onboard,missing country,

125,flowers-gift,nw,onboard,blank SLS,human resource

126,flowers-gifts,nw,onboard,missing country,Human resource
126,onboard gift,nw,onboard,missing country,

;

proc sort data= have; by reason; run;

data haveuniq;
set have;
by reason;
if first.reason then output;
run;

the output which im expecting is shown below:

123,flowers-gift,nw,onboard,blank SLS,human resource
123,flowers-gifts,nw,onboard,missing country,Human resource

124,flowers-gift,nw,onboard,blank SLS,human resource
124,flowers-gifts,nw,onboard,missing country,Human resource

125,flowers-gift,nw,onboard,blank SLS,human resource
126,flowers-gifts,nw,onboard,missing country,Human resource

the output im getting is

this is what is confusing me.

Re: First. And last .

Tom — Fri, 30 Jun 2023 13:48:59 GMT

Why does the output confuse you? There are only two distinct values of REASON so you should only get two observations out when selecting based on distinct values of REASON.

What is the reason that you want to remove the three observations you want removed?

The only thing I can see that would produce that output would be to just delete the observations with blank values of DEPARTMENT.

data have ;
  infile cards dsd truncover ;
  input Id $ name :$20. function :$5. status :$20. reason :$20. department :$20.;
cards;
123,flowers-gift,nw,onboard,blank SLS,human resource
123,flowers-gifts,nw,onboard,missing country,Human resource
123,onboard gift,nw,onboard,missing country,
124,flowers-gift,nw,onboard,blank SLS,human resource
124,flowers-gifts,nw,onboard,missing country,Human resource
124,onboard gift,nw,onboard,missing country,
125,flowers-gift,nw,onboard,blank SLS,human resource
126,flowers-gifts,nw,onboard,missing country,Human resource
126,onboard gift,nw,onboard,missing country,
;


data expect ;
  infile cards dsd truncover ;
  input Id $ name :$20. function :$5. status :$20. reason :$20. department :$20.;
cards;
123,flowers-gift,nw,onboard,blank SLS,human resource
123,flowers-gifts,nw,onboard,missing country,Human resource
124,flowers-gift,nw,onboard,blank SLS,human resource
124,flowers-gifts,nw,onboard,missing country,Human resource
125,flowers-gift,nw,onboard,blank SLS,human resource
126,flowers-gifts,nw,onboard,missing country,Human resource
;


data no_miss_department;
  set have;
  if not missing(department) then output;
run;

proc compare data=no_miss_department compare=expect;
run;

Re: First. And last .

Tom — Fri, 30 Jun 2023 15:33:02 GMT

I ran a quick test to see what combination of BY variables would lead to 6 distinct combinations.

proc summary data=have chartype;
  class id--department / missing;
  output out=groups;
run;

proc freq order=freq; 
  tables _type_ / noprint out=counts;
run;

proc print data=counts;
  where count=6;
run;

And the result is:

Obs    _TYPE_    COUNT    PERCENT

 25    100010      6      1.76471
 26    100110      6      1.76471
 27    101010      6      1.76471
 28    101110      6      1.76471

So the minimum set is the first one, 100010, which means the first and fifth variables, ID AND REASON.

So sort by ID and REASON then filter by FIRST.REASON. (Or sort by REASON and ID and filter by first.ID) and you will get 6 observations out.

To get the exact observations you picked manually you will also need to sort by some of the other variables so that the one you want to select is the first one for that combination of ID and REASON.

This list of BY variables works for your example.

proc sort data=have ;
  by id reason name ;
run;

proc sort data=expect; 
  by id reason name ;
run;

data want;
  set have;
  by id reason name ;
  if first.reason;
run;

proc compare data=want compare=expect;
run;

Re: First. And last .

Reeza — Fri, 30 Jun 2023 15:27:44 GMT

@Akshaya_1397 wrote:

Hi,

I have a datset final in which there are columns like

Id,name,function,status,reason, department and has values

123,flowers-gift,nw,onboard,blank SLS,human resource

123,flowers-gifts,nw,onboard,missing country,Human resource

123,onboard gift,nw,onboard,missing country,

I need to take unique rows so I performed first. In the dataset

Data new;

Set final;

If first.id and first.name and first.department then output;

Run;

The output I'm getting is only the first row that is with the reason blank SSL but I also need the second row with missing country.

Could anyone please help me on this

Assuming your comment in Red is true, SQL is a good option.

You can also use SORT (see the examples below).

*make test data;

data class;
    set sashelp.class sashelp.class (obs=8);
run;

*select unique records;

proc sql;
    create table distinct_records as 
    select distinct * 
    from class;
quit;

proc sort data=class;
    by _all_;
run;

proc sort data=class noduprecs out=distinct_via_sort;
    by _all_;
run;

Re: First. And last .

mkeintz — Sat, 01 Jul 2023 00:00:21 GMT

Let's assume you have data sorted by MAJOR_KEY, INTERMEDIATE_KEY, MINOR_KEY. And for some of those triplets, there are duplicates you want to eliminate. If you do this

data want;
   set mydata;
   by major_key intermediate_key minor_key;
   if first.minor_key;
run;

You will get one record per unique triplet. It that your goal?