DATA Step, Macro, Functions and more

Finding obs in one table and output in another table

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

Finding obs in one table and output in another table


data  Table1(drop=sdt edt);
input sdt$ 1-9 edt$ 10-20 comm_cou;
stdate =input(sdt,date9.);
enddate=input(edt,date9.);
format stdate enddate ddmmyy10.;
cards;
12mar2011 13mar2011 usa
13mar2011 14mar2011 usa
14mar2011 15mar2011 usa
15mar2011 16mar2011 usa
16mar2011 17mar2011 usa
17mar2011 18mar2011 usa
18mar2011 19mar2011 usa
19mar2011 20mar2011 usa
20mar2011 21mar2011 usa

12mar2011 13mar2011 uk
13mar2011 14mar2011 uk
14mar2011 15mar2011 uk
15mar2011 16mar2011 uk
16mar2011 17mar2011 uk
17mar2011 18mar2011 uk
18mar2011 19mar2011 uk
19mar2011 20mar2011 uk
20mar2011 21mar2011 uk
;
run;


Table 2:

14mar2011 15mar2011 usa

Table 3:

15mar2011 16mar2011 usa


In the above table i am having the dates table_1 having the dates and comm_cou
in table_2 i am having dates and commn country i wnat table_3 which is having enddate in table 2 and it should be
stdate  of table_1 only with one observation only.


Accepted Solutions
Solution
‎12-27-2011 08:34 AM
PROC Star
Posts: 7,471

Finding obs in one table and output in another table


All Replies
Contributor
Posts: 66

Finding obs in one table and output in another table

I am having Table1 and Table2 i want Table3 as output.(I want the output in Table 3 as enddate of table2

=stdate of table1 to Table 3(AS Output)

PROC Star
Posts: 7,471

Finding obs in one table and output in another table

There are a number of ways to do what you want.  e.g.:

data  Table1;

  informat stdate enddate date9.;

  input  stdate enddate comm_cou $;

  format stdate enddate ddmmyy10.;

  cards;

12mar2011 13mar2011 usa

13mar2011 14mar2011 usa

14mar2011 15mar2011 usa

15mar2011 16mar2011 usa

16mar2011 17mar2011 usa

17mar2011 18mar2011 usa

18mar2011 19mar2011 usa

19mar2011 20mar2011 usa

20mar2011 21mar2011 usa

12mar2011 13mar2011 uk

13mar2011 14mar2011 uk

14mar2011 15mar2011 uk

15mar2011 16mar2011 uk

16mar2011 17mar2011 uk

17mar2011 18mar2011 uk

18mar2011 19mar2011 uk

19mar2011 20mar2011 uk

20mar2011 21mar2011 uk

;

run;

data  Table2;

  informat stdate enddate date9.;

  input  stdate enddate comm_cou $;

  format stdate enddate ddmmyy10.;

  cards;

14mar2011 15mar2011 usa

;

proc sql;

  create table table3 as

    select a.stdate, a.enddate, a.comm_cou

      from table1 as a

        right join table2 as b

          on a.stdate=b.enddate and

            a.comm_cou=b.comm_cou

  ;

quit;

Contributor
Posts: 66

Finding obs in one table and output in another table

Thqs art it worked and i want to keep directly table 3 only and changed the logi in the join as done liket this i am getting the warning


proc sql nowarn;
  create table table3 as
    select a.stdate, a.enddate, a.comm_cou
      from table1 as a
        right join table3 as b
          on a.stdate=b.enddate and
            a.comm_cou=b.comm_cou
  ;
quit;

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this
is a possible data integrity   problem.

How can i supress the warning tryed to keep nowarn but not working

Solution
‎12-27-2011 08:34 AM
PROC Star
Posts: 7,471

Finding obs in one table and output in another table

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 196 views
  • 3 likes
  • 2 in conversation