create a table with restrictions

Reply
Contributor
Posts: 62

create a table with restrictions

Hi,

I have this table with many variables:

PackageIDLenderIDDummy1Dummy2
29110
29210
29301
30101
30210
30300
30400
30500

From this table, I want to create a table which contains only the package with the restrictions following: (LenderID=1 and dummy1=1) and (LenderID NE 1 and dummy1=0)

My code was:

proc sql;

create table want as

select *

from table1

where (LenderID=1 and dummy1=1) and (LenderID NE 1 and dummy1=0);

quit;

This code doesn't work.

For example, in the table, the right code must keep the packageID 29 with the lines 1 and 3. (LenderIDs 1 and 3 in the package 29. LenderID 3 has a dummy1 of 1).

The right code must not keep the package 30 because LenderID 1 has not a dummy1 of 1. So, it must not keep the LenderIDs 3, 4 and 5 even if dummy1=0.

The right code must choose packageIDs where LenderID=1 with dummy1=1 and after, if the first condition is correct, other LenderIDs than 1 which have a dummy1 of 0 in the same packageID.

Thank you in advance for your replies.

PROC Star
Posts: 7,471

Re: create a table with restrictions

Logic problem.  A record can't meet both conditions.  Did you mean to use:

where (LenderID=1 and dummy1=1) OR (LenderID NE 1 and dummy1=0);


?


Contributor
Posts: 62

Re: create a table with restrictions

My code is completley wrong.

Inside each package, the first condition must be met and after, the second condition is also applied.

So, if a packageID doesn't not meet the first condition, the second condition is not executed. The code excludes the packageID in question.

It made me think. The code must surely contain:

group by PackageID;

ordre by PackageID;

or something like that.

PROC Star
Posts: 7,471

Re: create a table with restrictions

: either I completely misunderstand what you are trying to do, or you didn't try my suggested revision.

In your example you indicated that you wanted to keep the first and third records for packageID 29.  The condition that those two records meet is:

where (LenderID=1 and dummy1=1) OR (LenderID NE 1 and dummy1=0);

That same logic would select the 3rd, 4th and 5th records for packageID 30,

Since you aren't using any summary functions a group by clause wouldn't change your result, and an order by clause would only chance the order of the selected records.

Contributor
Posts: 62

Re: create a table with restrictions

Thank you for your reply.

When you said ''That same logic would select the 3rd, 4th and 5th records for packageID 30'', it is not exactly what my code must do.

In my example, because LenderID=1 has dummy1=0, the 3rd, 4th and 5th records for packageID 30 must not be selected in my new table (even if LenderID NE 1 and dummy1=0)

If LenderID=1 had dummy1=1, you were right.

The first condition must be met. And after, the second condition. It is why we cannot use ''AND'' or ''OR'' between the two pairs of brackets.

(I'm a newly arrived person on SAS communities. I can be wrong.)

The right code must not contain any line of packageID 30 at all.

PROC Star
Posts: 7,471

Re: create a table with restrictions

: Someone else will have to show you how it could be done with sql.  With a data step, I think you are looking for something like:

data table1;

  infile cards dlm='09'x;

  input PackageID LenderID Dummy1 Dummy2;

  cards;

29 1 1 0

29 2 1 0

29 3 0 1

30 1 0 1

30 2 1 0

30 3 0 0

30 4 0 0

30 5 0 0

;

data want (drop=flag);

  set table1;

  by PackageID;

  retain flag;

  if first.PackageID then do;

    flag=0;

    if LenderID eq 1 and Dummy1=1 then flag=1;

  end;

  if flag then do;

   if (LenderID eq 1 and dummy1 eq 1) OR

     (LenderID NE 1 and dummy1 eq 0) then output;

  end;

run;

Super User
Posts: 5,503

Re: create a table with restrictions

If I understand the problem, this should come pretty close:

data want;

   length wanted $ 5;

   wanted='No';

   do until (last.PackageID);

      set have;

      by PackageID LenderID;

      if wanted='No' and LenderID=1 and dummy1=1 then wanted='Maybe';

      else if wanted='Maybe' and LenderID ne 1 and dummy1=0 then wanted='Yes';

   end;

   do until (last.PackageID);

      set have;

      by PackageID;

      if wanted='Yes' then output;

   end;

   drop wanted;

run;

Contributor
Posts: 62

Re: create a table with restrictions

Posted in reply to Astounding


Thank you for your replies!

Ask a Question
Discussion stats
  • 7 replies
  • 279 views
  • 6 likes
  • 3 in conversation