BookmarkSubscribeRSS Feed
jwhite
Quartz | Level 8

Hi Community,

 

So, using DI Studio I can group columns and create a count(*) coulmn that captures the number in that group, but is there a way to locate the first record in that group?

 

I want to find the first time one of the duplicates appears, because I want to keep that one and close out the rest on a fact table.

 

I was thinking if I could have a column that had each observation within the group numbered I could simply look for the '1' in this column. Or if I could find another way to flag one record within the group, that'd be great.

 

Seems like a simple thing to do.

 

Thanks!

4 REPLIES 4
LinusH
Tourmaline | Level 20

I'm not sure how you would apply this logic into a job/flow, and how would the Table Loader step work...?

Nevertheless, I don't think that a standard transformation could do this in one step.

If your data is sorted, a User Written code with if first.your_group then counter+1; would do the trick.

Data never sleeps
jwhite
Quartz | Level 8

Fortunately, I don't need this in a Table Loader step, but for the time being just for some analysis. I was thinking I could do it in an Extract, but maybe I would need to use a User Written piece, and sort the code first.

 

I'm a SAS beginner, so could you flesh out your suggestion just a bit more?

 

Thanks, Linus!

LinusH
Tourmaline | Level 20
If it's just for analysis DI Studio may not be the ideal environment. Rather Enterprise Guide or SAS Studio.
Either way, the data step would be something like:

data want;
Set have;
By myid;
If first.myid then counter=1;
Else counter +1;
Run;
Data never sleeps
TomKari
Onyx | Level 15
SQL typically isn't a good option for things like the "first" record in a group, UNLESS you can identify it by some combination of min or max of variables, in which case it's usually quite easy. Provide a few more details about what you need. Personally, I agree with @LinusH that you should use EG or Studio if it's exploratory. Tom

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 909 views
  • 0 likes
  • 3 in conversation