topic Re: Proc sql select into in SAS Programming

Proc sql select into

chennupriya — Thu, 28 Apr 2016 17:29:28 GMT

Hi ,

i have this dataset and i have written a macro to validate its pulling what is in excel

Item	Item Name
Books	A01
Books	A22
Books	A23
Books	B01
Books	C25
Books	C03
Books	C45
Books	C46

%macro price;

proc sql;

select ('Item Name'n) into :iname separated by "*" from x

quit;

% put &iname;

%do y=1 to 8;

%let xz=%scan(&iname,&y,*);

%put &xz;

proc sql;

create table zz

(

ffnam char 4

);

quit;

proc sql;

insert into zz

values ("&xz");

quit;

%end;

%mend;

%price;

% put &iname resolves to A01*A22*A23*B01*C25*C03*C45*C46;

HOW DO I TEST THIS HAS GIVEN ALL RESULTS OF TEM NAME FROM MY DATASET ?

% PUT &XZ RESOLVES TO A01 when y=1 so i am inserting each value of &xz into zz but when i open zz dataset i see only

C46 how to get all values of A01 TO C46 into zz dataset ?

Can anyone pls help me ?

Re: Proc sql select into

Astounding — Thu, 28 Apr 2016 17:43:23 GMT

Isn't your program the same thing as this:

proc sql;

create table zz as select 'Item Name'n as ffnam from x;

quit;

You wouldn't need to validate the result if you got rid of all the macro language.

Re: Proc sql select into

Reeza — Thu, 28 Apr 2016 17:46:25 GMT

How does this validate your data? Wouldn't it be the same as your import?

Re: Proc sql select into

chennupriya — Thu, 28 Apr 2016 18:10:27 GMT

HI ,

i am concerned about getting all the values of item name into zz dataset but rite i am getting only C46

Re: Proc sql select into

Astounding — Thu, 28 Apr 2016 18:34:24 GMT

That's happening because you re-create the table zz each time through the macro loop. Move the SQL step that creates zz outside of the loop. Or better yet, get rid of the macro language entirely.

Re: Proc sql select into

chennupriya — Thu, 28 Apr 2016 18:42:20 GMT

Hi ,

Then how do i get

% PUT &XZ RESOLVES TO A01 when y=1 so i am inserting each value of &xz into zz but when i open zz dataset i see only

C46 how to get all values of A01 TO C46 into zz dataset ?

so how do i get all values of &xz into zz dataset ? how do i chnage the code ?

Re: Proc sql select into

Astounding — Thu, 28 Apr 2016 18:44:30 GMT

Move the SQL step that creates zz. Don't move the SQL step that inserts into ZZ.

The moved code should go just before the %do statement.

Re: Proc sql select into

chennupriya — Thu, 28 Apr 2016 19:10:04 GMT

Thank you it worked

Re: Proc sql select into

chennupriya — Thu, 28 Apr 2016 20:12:18 GMT

Hi ,

Now what should i use to check whether the values for item name and the values in zz dataset match each other ?

Thanks

Re: Proc sql select into

ballardw — Thu, 28 Apr 2016 21:04:20 GMT

@chennupriya wrote:

Hi ,

Now what should i use to check whether the values for item name and the values in zz dataset match each other ?

Thanks

What are your rules to determine if it matches?