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

I have 5 names in table1 and 5 names in table2..

Table1Table2Table
AFA
BGF
CHB
DIG
EJC
  H
  D
  I
  E
  J

 

want to create a table with output picking values alternatively from two tables

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

A one to one merge?

 

data table1;

input var1 $;

datalines;

A

B

C

D

E

;

 

 data table2;

 input var2 $;

 datalines;

 F

 G

 H

 I

 J

 ;

 

 data table;

 set table1 ;

 set table2;

 var=var1;

 output;

 var=var2;

 output;

 drop var1 var2;

 run;

View solution in original post

7 REPLIES 7
Shmuel
Garnet | Level 18

Add a temporary id:

    For table1 id should be: 1 3 5 ...

    For table2 id should be 2 4 6 ...

then merge the two tables and drop the temporary id.

data t_table1;
  set table1;
        retain id 1;
        output;
        id+2;
run;

data t_table2;
 set table2;
       retain id 2;
       output;
       id+2;
run;

data table;
 merge t_table1 t_tabl2;
  by id;
       drop id;
run;
novinosrin
Tourmaline | Level 20

A one to one merge?

 

data table1;

input var1 $;

datalines;

A

B

C

D

E

;

 

 data table2;

 input var2 $;

 datalines;

 F

 G

 H

 I

 J

 ;

 

 data table;

 set table1 ;

 set table2;

 var=var1;

 output;

 var=var2;

 output;

 drop var1 var2;

 run;

Naveen45
Fluorite | Level 6

Thank you for your reply @novinosrin .... its working....

 

can you explain me the logic as i dint get it

novinosrin
Tourmaline | Level 20

The logic: Since you mentioned two tables, I created two sample tables each containing a variable as you may have noticed var1 in table1 and var2 in table2.

Typically, the entire SAS programming revolves around the following major concepts:

1. Bring the record into PDV(memory) area where you process

2. Reset values in PDV by editing, copying or computing any values of any variables. So the new variable var takes the value of var1 

3. Before resetting again, make sure to use an explicit output so that you don't miss out on the requirement to have the alternative values

4. reset the value of var again this time with value if var2

5. Use another explicit output statement to get the values written to the dataset.

 

Important concepts in SAS:

1. Implicit pass/output and return

2. Explicit output overriding implicit output still implicit return

3. Zipping through the dataset in memory with a loop(do until eof)

4. Zipping through by groups in memory with a loop(do until(last.by_variable)

5. Look above and Look ahead. (Lag, retain, merge with firstobs=2 and so on)

6. Look up the entire table, column for multiple values-- easy way hashes

7. Familiarise with functions however i don't emphasise this as I believe the above 6 will lead you look for functions or even create your own

 

Hope that helps

Naveen45
Fluorite | Level 6

Thankyou for your explanation @novinosrin

Naveen45
Fluorite | Level 6

output is not comming in single coloumn

its commig in two different coloumns like this

 

name1name2
A 
 F
B 
 G
C 
 H
D 
 I
E 
 J

 

 

novinosrin
Tourmaline | Level 20

That's the reason I reset the values to var instead of var1 and var2 in my example above.

var=var1;

 output;

 var=var2;

 output;

 

This makes sure the output is in single column var  Please run my eample, you will get it

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
  • 7 replies
  • 1025 views
  • 2 likes
  • 3 in conversation