how to - PROC SQL from file?

[StaticFX]

Im still new to SAS. I am trying to condense a long script by using some PROC SQL statements.

currently there is a data step (changed info )

data tbs_tmp (KEEP=FIELD1 FIELD1 FIELD1 FIELD1 FIELD1);
set '/home/xxxx/xxxxxx/tbs';
run;

then I am running a SQL statement pulling from tbs_temp

is there a way to do it directly from the "file"?

CREATE TABLE TBS AS
SELECT X,Y,Z FROM '/home/xxxx/xxxxx/tbs'

?
Thanks!

 

[klaz2002]

Why are you using Proc SQL? Do you need the data sorted? Please tell us more of what you need done.
klaz

 

[StaticFX]

yes, sorted & nodups

SELECT DISTINCT X,Y,Z FROM '/home/xxxx/xxxx/tbs' WHERE X=1 AND Y=2 ORDER BY X,Y,Z

that way I can squish 2 steps into 1.

 

[klaz2002]

Why not use the Proc Sort like this?

proc sort
  data = in_tbs(KEEP=FIELD1 FIELD1 FIELD1 FIELD1 FIELD1)
  out  = TBS nodupkey;
  by X Y Z;
  where x=1 and y=2;
run;

Of course in_tbs has to be an existing SAS dataset. I think that the first data step in your sample has more options than you display here. I believe that you are reading a non SAS datasource into SAS. In that case there is an option missing on your statement and there is nothing you can do to compress the script. Now if the data is in a SAS compliant format such as ACCESS etc youc an use the SAS\ACCESS engine and treat that data as a sas datasource. If that is the case my example using the proc sort would shorten your code.

I hope this helps you.
Klaz

 

[StaticFX]

here is the real original code.. with the path name x'd out


data tbs (KEEP=CUSACCID DOCNUMB SERVITEM LINESTAT ACCTNO SREQSTAT ACCTPRCD
LMODDATE ACTIND ANI IDENTCD CKTDESID COMPNAME);
set '/home/xxxx/xxxx/tbsanis';

IF ACCTPRCD = 'NVA' AND SYST = 'EAST' AND LINESTAT='6';

proc sort nodups data=tbs;
by CUSACCID SERVITEM DOCNUMB LINESTAT ACCTNO SREQSTAT ACCTPRCD
LMODDATE ACTIND ANI IDENTCD CKTDESID COMPNAME;
run;


any way to shorten that to 1 proc?

 

[klaz2002]

Have you tried to do the following?

proc sort data='/home/xxxx/xxxx/tbsanis'
          out = TBS nodupkey;
        by CUSACCID SERVITEM DOCNUMB LINESTAT ACCTNO SREQSTAT ACCTPRCD
           LMODDATE ACTIND ANI IDENTCD CKTDESID COMPNAME;
 where ACCTPRCD = 'NVA' AND SYST = 'EAST' AND LINESTAT='6';
run;

I don't use UNIX so I am not sure that the 'path' works in this proc.

 

[StaticFX]

FANTASTIC... that shaves off a good 30 seconds!
Thank you!