@Kurt_Bremser Thanks for checking, but I am looking for solution with the below code. I don't want to remove the macro variables since I have a lot of variables to pass through & expecting the results as I mentioned in my original request. I know I can achieve by other methods, but looking to solve by tweaking the below code only as I have a buch of variables to pass through. Please let me know if there is a way to solve it.
proc format;
value gfmt
low-0 = " 0"
1-1000 = " 1-1,000"
1001-10000 = " 1,001-10,000"
10001-20000 = " 10,001-20,000"
20001-30000 = " 20,001-30,000"
30001-40000 = " 30,001-40,000"
40001-50000 = " 40,001-50,000"
50001-60000 = " 50,001-60,000"
60001-high = " GT 60,000"
other = " ND"
;
data test2;
set test1;
if VAR3 = "ND" then VAR4=. ;
else VAR4=input(VAR3,12.);
run;
%macro M_Format (app);
proc sql;
create table test3 as
select count(VAR2) as Freq_Month1
,strip(&app.) as Group
,case when VAR1 = "XXXX" and ID in (101,102,103,104,105,106) then "USA"
when VAR1 = "YYYY" and ID in (101,102,103,106) then "UK"
when VAR1 = "ZZZZ" and ID in (101,102,103,104) then "EUROPE"
when VAR1 = "VVVV" and ID ne . then "ROI"
when VAR1 = "AAAA" and ID in (101,102,103,104) then "JAPAN"
else VAR1 end as Region
from test2
group by Group, Region
order by group, Region
;
quit;
%mend;
%M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end);
If you can define in which ways the code needs to be made dynamic, I could wrap my code into a macro again so it fits your needs. Your code can never create the desired order reliably, because of the way strings order up along the ASCII character table.
@Data_User wrote:
I also tried with the below format as well, but no luck.
proc format;
value gfmt
low-0 = " 0"
1-1000 = " 1-1,000"
1001-10000 = " 1,001-10,000"
10001-20000 = " 10,001-20,000"
20001-30000 = " 20,001-30,000"
30001-40000 = " 30,001-40,000"
40001-50000 = " 40,001-50,000"
50001-60000 = " 50,001-60,000"
60001-high = " GT 60,000"
other = "ND"
;
run;
What type of values do you expect to get the formatted value of ND? The format as stated will assign ND to missing a decimal values between your stated endpoints such as 0.5, 1000.1, 20000.3
@ballardw "ND"s can be missing and other values which falls outside the format range I have defined.
Hi All, thanks very much for all your contributions on this. I am now able to achieve what I want. I really appreciate your help on this. Have a good weekend 🙂
Best Regards,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.