BookmarkSubscribeRSS Feed
flachboard
Fluorite | Level 6

Hello, I'm running a SAS program that was put in place before I started at my job. The issue that I'm having is that one of the fields is only being populated on the last unique line. Example below.

IDCATGRSG
D001E33F8

D001

E33F8
D001ABCE33F8
D001E44F9
D001DEFE44F9
D001GHIE44F10
D002E55F11
D002E55F11
D002JKLE55F11

I want the CAT field to be populated for every line (not just the final unique line if that makes sense).

Does anyone know what I should look for in my code to fix this? I'm very new to SAS unfortunately.

5 REPLIES 5
mohamed_zaki
Barite | Level 11

data catx ;

set have;

if CAT ~= "";

run;

proc sql;

craete table want as

select a.ID, b.CAT, a.GR,a.SG

from have a, catx b

where a.ID = b.ID

AND a.SG = b.SG

AND a.GR = b.GR;

run;

Jagadishkatam
Amethyst | Level 16

i am not sure if i understood your question correctly

Hope this is the output you are expecting

if this is the case, then please try

proc sort data=have;

by id gr sg descending cat  ;

run;

data want;

set have;

by id gr sg  ;

retain cat_;

if first.sg then cat_=cat;

run;

Thanks,

Jag

Thanks,
Jag
art297
Opal | Level 21

Incorporating an inline view might make the code easier to follow. E.g.:

proc sql;

  create table want (drop=_cat) as

    select *

      from (select id,cat as _cat,max(cat) as CAT,gr,sg

              from have

                group by id,gr,sg)

  ;

quit;

Reeza
Super User

What does your input data look like?

PGStats
Opal | Level 21

Ideally you should fix the code that genarates that table. We can't help without knowing what that code is. But you might simply want to correct for its flaws by adding a step like:

proc sql;

create table want as

select id, max(cat) as CAT, gr, sg

from have

group by id;

quit;

PG

PG

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 965 views
  • 0 likes
  • 6 in conversation