Looking for ways to retrieve some data values from one dataset to another dataset

[Amen]

Hi,

I have to 2 different SAS programs that generate 2
different SAS tables named table1 and table2. Table1 and table2 codes below:

data table1;

length state $3 Chevy $7 GMC $7 Honda $7 miles $20;

infile datalines dlm=',' dsd;

input state Chevy GMC Honda miles;

return;

datalines;

GA,$1200,$4300,$3000,"High Mileage Cars"   

;

run;

proc print data=table1;

run;

data Table2;

length Weeks $7 Prices $7 desc $32;

infile datalines dlm=',' dsd;

input Weeks Prices desc ;

return;

datalines;

' ',' ',"Insurance Track"

First,Chevy,"Old Cars without Insurance"

second,GMC,"Old Cars with Insurance"

Third,Honda,"Old Cars with Insurance"

;

run;

proc print data=table2;

run;

I want to write a program that would enable me to retrieve the values of the 

columns Chevy, GMC, Honda, and miles, from table1 and place them at their respective
positions in table2. Here is the design below:

         Data table: Miles

_______________________________________________

Weeks       Prices          desc

_______________________________________________

                     Insurance Track

First       Chevy    OldCars without Insurance

second     GMC       Old Cars with Insurance

Third       Honda    OldCars with Insurance

_______________________________________________

But the final output should contain the below data for respectively Chevy, GMC, Honda, miles:

            Data table: High Mileage Cars

_______________________________________________

Weeks       Prices          desc

_______________________________________________

                      Insurance Track

First       $1200    Old Cars without Insurance

second     $4300     Old Cars with Insurance

Third       $3000    Old Cars with Insurance

______________________________________________

Variable PRICES would be going on the user interface and treated as a global variable.

Thanks for help

[LinusH]

Your "Table" needs to be transposed so that each row has the combined key of State and Prices (?).

Then you just join the two tables.

[RW9]

Alternatively you can use update in SQL with a subquery:

data table1;

  length state $3 Chevy $7 GMC $7 Honda $7 miles $20;

  infile datalines dlm=',' dsd;

  input state Chevy GMC Honda miles;

datalines;

GA,$1200,$4300,$3000,"High Mileage Cars"  

;

run;

data Table2;

  length Weeks $7 Prices $7 desc $32;

  infile datalines dlm=',' dsd;

  input Weeks Prices desc ;

datalines;

' ',' ',"Insurance Track"

First,Chevy,"Old Cars without Insurance"

second,GMC,"Old Cars with Insurance"

Third,Honda,"Old Cars with Insurance"

;

run;

proc sql;

  update TABLE2 A

  set PRICES=case when PRICES="Chevy" then (select CHEVY from TABLE1)

                  when PRICES="GMC" then (select GMC from TABLE1)

                  when PRICES="Honda" then (select HONDA from TABLE1)

                  else "" end;

quit;

[MikeZdeb]

Hi.  You got answers, so this is just a comment.  The two data steps could also be written ...

data table1;

infile datalines dsd;

input state :$2. (Chevy GMC Honda) (:comma.) miles :$20.;

datalines;

GA,$1200,$4300,$3000,"High Mileage Cars" 

;

data Table2;

infile datalines dsd;

input weeks :$7. prices :$7. desc :$32.;

datalines;

' ',' ',"Insurance Track"

First,Chevy,"Old Cars without Insurance"

second,GMC,"Old Cars with Insurance"

Third,Honda,"Old Cars with Insurance"

;

#1  Rather than repeat the variable names in both a LENGTH and INPUT statements, you can assign variable attributes with INFORMATS in the INPUT statement.  Since you are using LIST input, use a COLON modifier prior to the INFORMAT.  The COMMA informat for the three prices will ignore that $-sign and read the data as numeric.  If you need that $-sign later, assign a DOLLAR format to the variables.

#2  The DSD in the INFILE statement implies a comma delimiter so youi don't need the DLM option.

#3  You don't need a RETURN statement to cycle through the data step.

#4  You don't need a RUN statement following a data step that ends with a DATALINES file.

[Haikuo · Accepted solution]

Given your data as it, the simplest way I see would be leveraging VVALUEX().

data want;

retain Weeks Prices desc;

if _n_=1 then set table1;

set table2(rename=prices=_p);

prices=vvaluex(_p);

keep Weeks Prices desc;

run;

[Amen]

Thanks Hai, questions for you.

Why do I get this note message? :      Argument to function vvaluex is not a valid variable name.

                                                             Invalid argument to function vvaluex at line

[MikeZdeb]

Hi.  This message ...

NOTE: Argument to function VVALUEX is not a valid variable name:  .

NOTE: Invalid argument to function VVALUEX at line 23 column 8.

Weeks=  Prices=  desc=Insurance Track state=GA Chevy=1200 GMC=4300 Honda=3000 miles=High Mileage Cars _p=  _ERROR_=1 _N_=1

occurs since for one of the observations in TABLE2, the value of the variable variable PRICES (renamed _P and used as the argument in the VVALUEX function) is MISSING.

You could modify the code (not sure what the RETAIN was for) ...

data want;

if _n_=1 then set table1;

set table2(rename=prices=_p);

if ^missing(_p) then prices=vvaluex(_p);

keep Weeks Prices desc;

run;