Hi,
Do we have any function in proc sql which is equivalent to "vtype" in Data step?. This is to check whether the field is a character or numeric and based on that passing it through a condition.
For example:
(Case when vtype(field) = 'C' then condition1 else condition2 end)
I appreciate any thoughts and suggestions on this. Thanks, DU
You can make a macro variable to hold this: data check_v1; infile datalines dsd missover dlm="#"; input var1 $ var2 $ var3 var4 var5; datalines; ND# ND#100#11#13 1500#ND#100#23#53 ND #1000#200#934#598 ND#ND #200#433#624 1000#2000#300#548#6889 1000#1000#300#548#6859 1000#2000#100#872#638 2000#3000#200#54987#879 2000#3000#200#549#548 ; RUN; %MACRO CH(VAR); PROC SQL; select case when type='char' then "STRIP(&VAR.)" else "&VAR." end as temp length=40 into : condition from dictionary.columns where libname='WORK' and memname='CHECK_V1' and upcase(name)="%upcase(&var)"; CREATE TABLE CHECK_V2_&VAR. AS SELECT &condition AS GROUP, SUM(VAR3) AS SUM3, SUM(VAR4) AS SUM4 FROM CHECK_V1 GROUP BY 1; QUIT; %MEND; %CH(VAR1) %CH(VAR2) %CH(VAR3)
If only vtype would work in SQL.
But maybe this is a way?
proc sql;
select
case when (select type from dictionary.columns where libname='SASHELP' and memname='CLASS' and name='Name') = 'char' then 'CHAR' else 'NUM' end as coltype
from sashelp.class;
run;
Will get tedious with many variables but it does get the job done. May you can macro-fy this to make it more shorter.
Hope this helps,
- Jan.
@jklaverstijn I am sorry, I am not able to understand it. Can you please suggest how can I achieve it from the below code?
data check_v1;
infile datalines dsd missover dlm="#";
input var1 $ var2 $ var3 var4 var5;
datalines;
ND# ND#100#11#13
1500#ND#100#23#53
ND #1000#200#934#598
ND#ND #200#433#624
1000#2000#300#548#6889
1000#1000#300#548#6859
1000#2000#100#872#638
2000#3000#200#54987#879
2000#3000#200#549#548
;
RUN;
%MACRO CH(VAR);
PROC SQL;
CREATE TABLE CHECK_V2_&VAR. AS
SELECT (case when vtype(&VAR.)='C' then STRIP(&VAR.) else &VAR. end) AS GROUP,
SUM(VAR3) AS SUM3,
SUM(VAR4) AS SUM4
FROM CHECK_V1
GROUP BY 1;
QUIT;
%MEND;
%CH(VAR1);
%CH(VAR2);
%CH(VAR3);
Thanks
DU
You could use
select cats(&VAR.) as GROUP
if you dont mind always making GROUP a string
Thanks for contributing on this. I really appreciate it.
Rreate a table with the required formats and use PUTC instead.
You can then use a data step to get the types or a query to the Dictionary tables.
You can make a macro variable to hold this: data check_v1; infile datalines dsd missover dlm="#"; input var1 $ var2 $ var3 var4 var5; datalines; ND# ND#100#11#13 1500#ND#100#23#53 ND #1000#200#934#598 ND#ND #200#433#624 1000#2000#300#548#6889 1000#1000#300#548#6859 1000#2000#100#872#638 2000#3000#200#54987#879 2000#3000#200#549#548 ; RUN; %MACRO CH(VAR); PROC SQL; select case when type='char' then "STRIP(&VAR.)" else "&VAR." end as temp length=40 into : condition from dictionary.columns where libname='WORK' and memname='CHECK_V1' and upcase(name)="%upcase(&var)"; CREATE TABLE CHECK_V2_&VAR. AS SELECT &condition AS GROUP, SUM(VAR3) AS SUM3, SUM(VAR4) AS SUM4 FROM CHECK_V1 GROUP BY 1; QUIT; %MEND; %CH(VAR1) %CH(VAR2) %CH(VAR3)
Awesome. It's working. Thanks very much for this 🙂 Really appreciate it..
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.