vtype function in proc sql

[Data_User]

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

[jklaverstijn]

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.

[Data_User]

@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

[ChrisNZ]

You could use

select cats(&VAR.) as GROUP

if you dont mind always making GROUP a string

[Data_User]

Thanks for contributing on this. I really appreciate it.

[Reeza]

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. 

[Ksharp · Accepted solution]

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)

[Data_User]

Awesome. It's working. Thanks very much for this 🙂 Really appreciate it..