DATA Step, Macro, Functions and more

vtype function in proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

vtype function in proc sql

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


Accepted Solutions
Solution
‎03-09-2017 09:34 AM
Super User
Posts: 9,681

Re: vtype function in proc sql


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)





View solution in original post


All Replies
Super Contributor
Posts: 408

Re: vtype function in proc sql

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.

 

 

Contributor
Posts: 43

Re: vtype function in proc sql

@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

PROC Star
Posts: 1,562

Re: vtype function in proc sql

You could use

select cats(&VAR.) as GROUP

if you dont mind always making GROUP a string

Contributor
Posts: 43

Re: vtype function in proc sql

Thanks for contributing on this. I really appreciate it.

Super User
Posts: 17,840

Re: vtype function in proc sql

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. 

Solution
‎03-09-2017 09:34 AM
Super User
Posts: 9,681

Re: vtype function in proc sql


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)





Contributor
Posts: 43

Re: vtype function in proc sql

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 207 views
  • 4 likes
  • 5 in conversation