BookmarkSubscribeRSS Feed
MilenkoAndreas
Calcite | Level 5

Hi everyone! I'm kind of new in SAS Guide. Right now Im in the process of shifting excel reports to guide reports.

I have a big problems managing rows in general.  I want to delete rows according to a specific criteria. In the Excel report I sort the matrix with three criterias then I create an extra column with a formula like this: if C1=C2 and D1=D2 and H1=H2, L1= "Yes", "No", after that I filter this column and select only the "Yes" value. Then I delete every row that has the "Yes" value excepting the rows that have a specific value in another column. I guess this is a very standart procedure for an Excel user, but in guide it seems to be more complex. Im not so sure how to do it because I know that the query builder gives you the option to delete duplicated rows but I want to delete rows that are duplicated in some columns not in every column, and besides I want to select which one of that group, not to delete. I would really appreciate any kind of help that I could get. If somebody doesnt get the question I would gladly explain it throughly.

Kind regards,

Milenko

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

You might need to consider a code node and a data step using the lag function.  You would have to sort of flip your criteria from using the next row to criteria using the previous row.  The lag function allows a data step to look at the previous record.

First step would be to sort your dataset as you need and then do something like:

data want (drop= lagc lagd lagh);

set have;

lagc=lag(c);

lagd=lag(d);

lagh=lag(h);

if c=lagc and d=lagd and h=lagh then duprec='Y';

else duprec='N';

if duprec='N' then output;

run;

MilenkoAndreas
Calcite | Level 5

DBailey,

Thank you so much for your help! Im not so sure what a lag function is, but I will google it! :smileygrin:

Thank you so much for your help!

Kind regards,

Milenko

Reeza
Super User

If you're removing duplicates you may want to look at the options for removing duplicates in the SORT task instead. Or consider using DISTINCT in the QUERY builder as well.

The key to switching from Excel to SAS EG is to learn the best method in SAS EG, which may not be the same as the method in Excel. It may be better to explain what you're trying to do in more detail and get the best method in SAS EG. 

ballardw
Super User

If you provide several rows of data, dummy is okay as long as it exhibits the different choices likely to occur, and what the final output should look like it will help us provide answers.

I suspect that in SAS you may not even need any thing more complicated than a WHERE dataset option.

For example to print the records from the SAS supplied dataset SASHELP.CLASS where we don't want to print if the record is Female and Age is 13:

Proc print data=sashelp.class (where=( not(sex='F' and age=13)));

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4920 views
  • 3 likes
  • 4 in conversation