DATA Step, Macro, Functions and more

Proc sql select into

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 125
Accepted Solution

Proc sql select into

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 ?

 

 


Accepted Solutions
Solution
‎04-28-2016 03:09 PM
Super User
Posts: 5,499

Re: Proc sql select into

Posted in reply to chennupriya

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.

View solution in original post


All Replies
Super User
Posts: 5,499

Re: Proc sql select into

[ Edited ]
Posted in reply to chennupriya

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.

Super User
Posts: 19,773

Re: Proc sql select into

Posted in reply to chennupriya

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

Frequent Contributor
Posts: 125

Re: Proc sql select into

HI ,

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

Super User
Posts: 5,499

Re: Proc sql select into

Posted in reply to chennupriya

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.

Frequent Contributor
Posts: 125

Re: Proc sql select into

Posted in reply to Astounding

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 ?

Solution
‎04-28-2016 03:09 PM
Super User
Posts: 5,499

Re: Proc sql select into

Posted in reply to chennupriya

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.

Frequent Contributor
Posts: 125

Re: Proc sql select into

Posted in reply to Astounding

Thank you it worked

Frequent Contributor
Posts: 125

Re: Proc sql select into

Posted in reply to Astounding

Hi ,

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

 

Thanks

Super User
Posts: 11,343

Re: Proc sql select into

Posted in reply to chennupriya

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?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 761 views
  • 0 likes
  • 4 in conversation