topic delete all the records with perticular id in SAS Enterprise Guide

delete all the records with perticular id

brm — Fri, 27 May 2011 22:25:13 GMT

Hi,

If One coloum is missing,then i want to delete all the records under that id.

How can i do this using query analyser in EG.Thanks in advance

brm

Re: delete all the records with perticular id

Patrick — Sat, 28 May 2011 05:10:02 GMT

Hi

Assuming you're talking about the Query Builder and that "deleting" means you don't want such observations in the output then one way to go could be (EG 4.2):

1. Tab "Select Data":
Define a computed column. Step 1: "advanced expression", step 2: select your variable with missings, step 3: under Summary choose "NMISS"

2. Tab "Select Data":
Have your ID variable under "Summary groups".

3. Tab "Filter": Add the calculated variable to "Filter the summarized data". In a "basic filter" set the condition to "Equals to" with value "0"

This should result in SQL code which looks like:

PROC SQL;
CREATE TABLE SASUSER.QUERY_FOR_HAVE AS
SELECT t1.id,
t1.var,
/* nmiss_var */
(NMISS(t1.var)) AS nmiss_var
FROM WORK.HAVE AS t1
GROUP BY t1.id
HAVING (CALCULATED nmiss_var) = 0;
QUIT;

HTH
Patrick

Re: delete all the records with perticular id

reddy19 — Tue, 31 May 2011 04:00:39 GMT

Patrick,

I did as you suggested...Here is the code generated

PROC SQL;
CREATE TABLE SASUSER.QUERY_FOR_BOOK13 AS
SELECT /* results */
(NMISS(t1.'Next Milestone Type'n)) AS results,
t1.DRC_CASE_ID,
t1.'Next Milestone Type'n
FROM SASUSER.BOOK13 AS t1
GROUP BY t1.DRC_CASE_ID
HAVING (CALCULATED results) = 0;
QUIT;

Getting below error in logs

ODS PDF(EGPDF) printed no output.
(This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE
statement.)

Please Tell me where i'm doing wrong.

Thanks,
reddy Message was edited by: reddy19

Re: delete all the records with perticular id

Patrick — Wed, 01 Jun 2011 09:12:28 GMT

Hi reddy

The generated code as such looks o.k. to me.

If you don't get an output then one reason could be that variable 'Next Milestone Type'n has missing values for every single distinct value of by group variable "DRC_CASE_ID".

I'm hardly using PDF output so can't say anything about this one.

First thing I would try is running the script with another output destination to see if it's an ODS PDF issue or something else.

HTH
Patrick

Re: delete all the records with perticular id

brm — Wed, 01 Jun 2011 17:48:43 GMT

Hi Patrick,

Below query is giving only the number of missing values

SELECT /* results */
(NMISS(t1.'Next Milestone Type'n)) AS results

So there is no way to exclude rows by id....because of that it is throwing error.

Thanks,
Bhavani.

Re: delete all the records with perticular id

Patrick — Wed, 01 Jun 2011 22:29:54 GMT

Hi brm

Of course you can exclude rows by ID. The code I've posted was tested and returned the expected result.

The important part is the "group by" ID and "having clause" selecting only ID's where NMISS returned 0 (=no missings).

What reddy19 doesn't get is an output - but that can be caused by having missings for every by group. We have to wait for reddy19's next posting to know more.