BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forumsguy
Fluorite | Level 6

Hi there,

I am new to SAS. I would like to seek help from all of you in order to create a combined table which can be later used to produce report using proc report, Wellm here it goes.

My first table is

Table1

data1 data2 data3     position

102        204       305 1

403        507       609 4

707        806       904 3

Table2

data4  data5  data6 position

1100  1200    1600  1

1700  1900    200    2

4000  7000    8000  5

Here the position variable is a reference variable which would determine my final tables position like if its 1 then that obs will be my first record m 4th then it will be my 4th record n so on

So my output should look like

Output

data1   data2   data3  data4   data5  data6

102       204      305      1100    1200    1600

.               .         .             1700    1900     200

707       806      904       .           .           .

403       507      609      .             .             .

.             .             .           4000    7000     8000

Is there any way to do this purely in Proc SQL or do we have to take use of data steps ????  Thanks a lot in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

Yes, you can use full join to get what you want.

data one;

input data1 data2 data3 position;

cards;

102        204       305 1

403        507       609 4

707        806       904 3

;

data two;

input data4  data5  data6 position;

cards;

1100  1200    1600  1

1700  1900    200   2

4000  7000    8000  5

;

proc sql;

  create table want as select data1,data2,data3,data4,data5,data6

     from one

             full join two

              on one.position=two.position;

           quit;

proc print;run;

                Obs    data1    data2    data3    data4    data5    data6

                     1      102      204      305      1100     1200     1600

                     2        .        .        .                  1700     1900      200

                     3      707      806      904         .        .        .

                     4      403      507      609         .        .        .

                     5        .        .        .                 4000     7000     8000

View solution in original post

2 REPLIES 2
Linlin
Lapis Lazuli | Level 10

Yes, you can use full join to get what you want.

data one;

input data1 data2 data3 position;

cards;

102        204       305 1

403        507       609 4

707        806       904 3

;

data two;

input data4  data5  data6 position;

cards;

1100  1200    1600  1

1700  1900    200   2

4000  7000    8000  5

;

proc sql;

  create table want as select data1,data2,data3,data4,data5,data6

     from one

             full join two

              on one.position=two.position;

           quit;

proc print;run;

                Obs    data1    data2    data3    data4    data5    data6

                     1      102      204      305      1100     1200     1600

                     2        .        .        .                  1700     1900      200

                     3      707      806      904         .        .        .

                     4      403      507      609         .        .        .

                     5        .        .        .                 4000     7000     8000

Ksharp
Super User

Another way is to use merge statement.

data one;
input data1 data2 data3 position;
cards;
102        204       305 1
403        507       609 4
707        806       904 3
;
run;

data two;
input data4  data5  data6 position;
cards;
1100  1200    1600  1
1700  1900    200   2
4000  7000    8000  5
;
run;
proc sort data=one;by position;run;
proc sort data=two;by position;run;
data want;
 merge one two;
 by position;
 drop position;
run;


Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 634 views
  • 3 likes
  • 3 in conversation