Help using Base SAS procedures

Output into an Access Database

Reply
Occasional Contributor
Posts: 8

Output into an Access Database

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.

Super Contributor
Posts: 490

Re: Output into an Access Database

Posted in reply to flachboard

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;

Trusted Advisor
Posts: 1,137

Re: Output into an Access Database

Posted in reply to flachboard

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
PROC Star
Posts: 7,492

Re: Output into an Access Database

Posted in reply to flachboard

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;

Super User
Posts: 19,870

Re: Output into an Access Database

Posted in reply to flachboard

What does your input data look like?

Respected Advisor
Posts: 4,934

Re: Output into an Access Database

Posted in reply to flachboard

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
Ask a Question
Discussion stats
  • 5 replies
  • 277 views
  • 0 likes
  • 6 in conversation