BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
siahal
Fluorite | Level 6

Hello all,

I kindly ask for a help with one issue.

While dealing with a macro loop in a data step i have a problem with app_number_of_children variable in prod_sorted table.

Despite the condition 'not missing(app_number_of_children) and app_number_of_children<1', and expected result 1 in wyj.vin_kat, there is value 3 apearing all the time.

As i can understand, the macro loop rewrite the value after each iteration.

How can I avoid it and get the right value?

 

Regards,

SH

 

 

	%global test;
	proc sql noprint;
		select warunek into :test separated by ' X'	
		from wyj.warunek order by warunek;
	quit;
	
	proc sql noprint; 
		select count(*) into :ile_war	
		from wyj.warunek; 
	quit;
%macro kategoryzuj;
	data wyj.vin_kat;	
		set wyj.prod_sorted;
			%do a=1 %to &ile_war;
				%let war = %scan(%scan(&test,&a,'X'),3,'A');
				%let zmien = %scan(%scan(&test,&a,'X'),1,'A');
				%let wart = %scan(%scan(&test,&a,'X'),2,'A');
				if &war then &zmien = &wart;
			%end;
		output;
	end; 
	run;

%mend;
%kategoryzuj;

 

1 ACCEPTED SOLUTION

Accepted Solutions
siahal
Fluorite | Level 6

Hi all,

 

The problem has been solved by dividing the data set by variables and conditions, checking separately and merging together.

 

Many thanks to all.

 

Regards,

SH

 

%macro kategoryzuj;

%global test;
%put &test;

	proc sql noprint; 
		select count(*) into :ile_war	
		from wyj.warunek; 
	quit;
%put &ile_war;

proc sql noprint;
select zmienna into :zmien_kat separated by " "
from wyj.lista_zmiennych_il;
quit;

proc sql noprint;
select count(zmienna) into :count_zm
from wyj.lista_zmiennych_il;
quit;

	data wyj.vin_kat;	
	set wyj.vin (keep = aid fin_period vin1 vin2 vin3);
	run;

%do c=1 %to &count_zm;

%let zmien = %scan(&zmien_kat,&c," ");
%put &zmien;

data t.warunek_iter;
set t.warunek;
zmienna = lowcase(trim(left(zmienna)));
run;

data t.warunek_iter;
set t.warunek_iter;
where zmienna = "&zmien";
run;

	proc sql noprint;
		select warunek into :test separated by ' X'	
		from wyj.warunek_iter order by warunek;
		quit;

	proc sql noprint;
	select count (*) into :iter
	from wyj.warunek_iter;
	quit;

	
	%do i = 1 %to &iter;

	%let war = %scan(%scan(&test,&i,'X'),3,'A');
	%let wart = %scan(%scan(&test,&i,'X'),2,'A');

	data war&c._&i;
	set wyj.vin (keep = aid &zmien);
	where &war;
	run;

	data war&c._&i;
	set war&c._&i;
	if &war then &zmien = &wart;
	run;

	%end;

	data war_&c;
	length aid $16;
	length &zmien 8;
	delete;
	run;

	%do j=1 %to &iter;

	data war_&c;
	set war_&c war&c._&j;
	run;

	%end;

proc sort data = war_&c;
by aid;
run;

proc sort data = wyj.vin_kat;
by aid;
run;

data wyj.vin_kat;
merge wyj.vin_kat(in=z) war_&c;		
	by aid;		
	if z;	
run;


%end;




%mend;

%kategoryzuj;

View solution in original post

6 REPLIES 6
ballardw
Super User

Run your macro with the options mprint and examine the code generated to see if the generated code is as expected.

 

options mprint;

%kategoryzuj;

siahal
Fluorite | Level 6

Thanks for your advice

but i still can't get an idea what to do;

 

	data vin_kat;	
		set wyj.vin;
		if not missing(app_number_of_children) and app_number_of_children <= 0 then app_number_of_children = 1;
		if 0 < app_number_of_children <= 1 then app_number_of_children = 2;
		if 1 < app_number_of_children then app_number_of_children = 3;
		output;
	run;

the question is - what will be outputed to the data set?

i get 3 in all observations.

If I have 0 before running the data step, the first condition changes it to 1, the second condition - to 2, and the last one - to 3.

It overwrites itself and the outputs

 

Do you have an idea how to apply only one condition - the right one?

 

Regards,

SH

Tom
Super User Tom
Super User

What are you trying to do?

Explain with a simple example using actual data. Post the data as a simple data step that either set values as constants or reads from in-line data.

ballardw
Super User

@siahal wrote:

Thanks for your advice

but i still can't get an idea what to do;

 

	data vin_kat;	
		set wyj.vin;
		if not missing(app_number_of_children) and app_number_of_children <= 0 then app_number_of_children = 1;
		if 0 < app_number_of_children <= 1 then app_number_of_children = 2;
		if 1 < app_number_of_children then app_number_of_children = 3;
		output;
	run;

the question is - what will be outputed to the data set?

i get 3 in all observations.

If I have 0 before running the data step, the first condition changes it to 1, the second condition - to 2, and the last one - to 3.

It overwrites itself and the outputs

 

Do you have an idea how to apply only one condition - the right one?

 

Regards,

SH


I suspect that you want ELSE in the code. Currently all 3 of your if statements are being evaluated and you see the result from the last one. Trace the logic: suppose the value is <=0, then the first if assigs value of 1, then the next if, since 1 <=1 reassigs to 2, then the last if assigns 3 because the 2 just assigned is >1.

If the value starts at between 0 and 1 then the first If doesn't execute but the second does setting 2 and then again the last sees the 2 and reassigns to 3.

 

data vin_kat;	
		set wyj.vin;
		if not missing(app_number_of_children) and app_number_of_children <= 0 then app_number_of_children = 1;
		else if 0 < app_number_of_children <= 1 then app_number_of_children = 2;
		else if 1 < app_number_of_children then app_number_of_children = 3;
		output;
run;
siahal
Fluorite | Level 6
Hi,
Thats right.
But I need to place this rule into The macro loop from The first code.
And I have no idea how to do this
SH
siahal
Fluorite | Level 6

Hi all,

 

The problem has been solved by dividing the data set by variables and conditions, checking separately and merging together.

 

Many thanks to all.

 

Regards,

SH

 

%macro kategoryzuj;

%global test;
%put &test;

	proc sql noprint; 
		select count(*) into :ile_war	
		from wyj.warunek; 
	quit;
%put &ile_war;

proc sql noprint;
select zmienna into :zmien_kat separated by " "
from wyj.lista_zmiennych_il;
quit;

proc sql noprint;
select count(zmienna) into :count_zm
from wyj.lista_zmiennych_il;
quit;

	data wyj.vin_kat;	
	set wyj.vin (keep = aid fin_period vin1 vin2 vin3);
	run;

%do c=1 %to &count_zm;

%let zmien = %scan(&zmien_kat,&c," ");
%put &zmien;

data t.warunek_iter;
set t.warunek;
zmienna = lowcase(trim(left(zmienna)));
run;

data t.warunek_iter;
set t.warunek_iter;
where zmienna = "&zmien";
run;

	proc sql noprint;
		select warunek into :test separated by ' X'	
		from wyj.warunek_iter order by warunek;
		quit;

	proc sql noprint;
	select count (*) into :iter
	from wyj.warunek_iter;
	quit;

	
	%do i = 1 %to &iter;

	%let war = %scan(%scan(&test,&i,'X'),3,'A');
	%let wart = %scan(%scan(&test,&i,'X'),2,'A');

	data war&c._&i;
	set wyj.vin (keep = aid &zmien);
	where &war;
	run;

	data war&c._&i;
	set war&c._&i;
	if &war then &zmien = &wart;
	run;

	%end;

	data war_&c;
	length aid $16;
	length &zmien 8;
	delete;
	run;

	%do j=1 %to &iter;

	data war_&c;
	set war_&c war&c._&j;
	run;

	%end;

proc sort data = war_&c;
by aid;
run;

proc sort data = wyj.vin_kat;
by aid;
run;

data wyj.vin_kat;
merge wyj.vin_kat(in=z) war_&c;		
	by aid;		
	if z;	
run;


%end;




%mend;

%kategoryzuj;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 860 views
  • 0 likes
  • 3 in conversation