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..
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.