topic Re: Output into an Access Database in SAS Procedures

Output into an Access Database

flachboard — Wed, 31 Dec 2014 14:10:10 GMT

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.

ID	CAT	GR	SG
D001		E33	F8
D001		E33	F8
D001	ABC	E33	F8
D001		E44	F9
D001	DEF	E44	F9
D001	GHI	E44	F10
D002		E55	F11
D002		E55	F11
D002	JKL	E55	F11

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.

Re: Output into an Access Database

mohamed_zaki — Wed, 31 Dec 2014 15:12:10 GMT

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;

Re: Output into an Access Database

Jagadishkatam — Wed, 31 Dec 2014 15:12:52 GMT

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

Re: Output into an Access Database

art297 — Wed, 31 Dec 2014 16:33:31 GMT

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;

Re: Output into an Access Database

Reeza — Wed, 31 Dec 2014 18:48:45 GMT

What does your input data look like?

Re: Output into an Access Database

PGStats — Wed, 31 Dec 2014 20:35:56 GMT

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;