topic Re: Looking for ways to retrieve some data values from one dataset to another dataset in Developers

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

Amen — Thu, 27 Aug 2015 12:34:31 GMT

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

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

LinusH — Thu, 27 Aug 2015 13:15:29 GMT

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.

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

RW9 — Thu, 27 Aug 2015 13:32:52 GMT

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;

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

MikeZdeb — Thu, 27 Aug 2015 13:57:29 GMT

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.

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

Haikuo — Thu, 27 Aug 2015 19:11:17 GMT

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;

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

Amen — Fri, 28 Aug 2015 15:42:21 GMT

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

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

MikeZdeb — Sat, 29 Aug 2015 15:13:36 GMT

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;