topic Re: vtype function in proc sql in SAS Programming

vtype function in proc sql

Data_User — Wed, 08 Mar 2017 19:29:08 GMT

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

Re: vtype function in proc sql

jklaverstijn — Wed, 08 Mar 2017 19:41:36 GMT

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.

Re: vtype function in proc sql

Data_User — Wed, 08 Mar 2017 19:47:40 GMT

@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

Re: vtype function in proc sql

Reeza — Wed, 08 Mar 2017 20:09:32 GMT

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.

Re: vtype function in proc sql

ChrisNZ — Wed, 08 Mar 2017 22:01:31 GMT

You could use

select cats(&VAR.) as GROUP

if you dont mind always making GROUP a string

Re: vtype function in proc sql

Ksharp — Thu, 09 Mar 2017 04:04:05 GMT


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)

Re: vtype function in proc sql

Data_User — Thu, 09 Mar 2017 14:33:32 GMT

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

Re: vtype function in proc sql

Data_User — Thu, 09 Mar 2017 14:35:05 GMT

Thanks for contributing on this. I really appreciate it.