Proc sql select into

[chennupriya]

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 ?

[Astounding]

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.

[Reeza]

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

[chennupriya]

HI ,

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

[Astounding]

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.

[chennupriya]

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 ?

[Astounding · Accepted solution]

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.

[chennupriya]

Thank you it worked

[chennupriya]

Hi ,

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

Thanks

[ballardw]

---

@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?