BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ccaudillo100
Obsidian | Level 7

Hello, 

 

Currently, I have a document that drops that imports into SAS and sometimes one of the columns is blank, and other times it is not. I am trying to use Proc Sql to edit the whole table in one swoop. The issue is, case - when does not work because the values are different when the import is blank. I was wanting to see if anyone had recommendations. currently, my import can sometimes look like:

 

 

Name Date Answered Total
Someone 1/19/2022   30

 

or like:

Name Date Answered Total
Someone 1/19/2022 10 30

 

 

 

The Proc SQL i was trying to use was

Proc Sql;
Create Table Want as 
select datepart('Date'n) as Date1 format  = mmddyy10.
		,Name as Source
		,case
		When Answered is Null then input("0",best.)
		When Answered is not Null then Answered
		end as Answered1
		,case 
		When Total is Null then input("0",best.)
		When Total is not Null then Total 
		end as Totals
From Have
;



 

 

This pull only works if the 2nd type of table is dropped for the import but if the 1st type is, the error I receive is:

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

 

 

Please help. I have tried CAST but it is not working for me.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

What is the original file type?  If it is a text file, like a CSV file, then don't IMPORT the file.  Just READ it and you can have complete control over the type.

 

To answer your question for a variable that should be a general number using the CATS() function to convert the values to character strings. 

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = cats(dropme);
  drop dropme;
run;  

If you want the target to be numeric then add an INPUT() function call.

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = input(cats(dropme),32.);
  drop dropme;
run;  

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

What is the original file type?  If it is a text file, like a CSV file, then don't IMPORT the file.  Just READ it and you can have complete control over the type.

 

To answer your question for a variable that should be a general number using the CATS() function to convert the values to character strings. 

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = cats(dropme);
  drop dropme;
run;  

If you want the target to be numeric then add an INPUT() function call.

data want;
  set have(rename=(badvar=dropme));
  length badvar $32;
  badvar = input(cats(dropme),32.);
  drop dropme;
run;  
ccaudillo100
Obsidian | Level 7

The original file type is xlsx. 

 

I want to automate the SAS code so ideally it would not need to be touched moving forward unless there was an error from something unexpected that did not come up during testing. 

Tom
Super User Tom
Super User

@ccaudillo100 wrote:

The original file type is xlsx. 

 

I want to automate the SAS code so ideally it would not need to be touched moving forward unless there was an error from something unexpected that did not come up during testing. 


If you want it automated then don't use an XLSX file as the source.

 

Generally this type of thing happens when the column is EMPTY in the XLSX file.  In that case PROC IMPORT or the XLSX libname engine will create a one byte character variable since it will use less space than an 8 byte floating point value.

 

So you could drop the variables that are character with length =1 that should have been numeric.   You can make a sample SAS dataset (or just the metadata for it) to drive the checking/fixing process.

 

%let droplist=;
proc sql noprint;
select nliteral(a.name) into :droplist separated by ' '
  from dictionary.columns a 
  inner join dictionary.columns b
  where a.libname='MYLIB' and a.memname='TEMPLATE'
    and b.libname='WORK' and b.memname='IMPORTED'
    and upcase(a.name) = upcase(b.name)
    and a.type='num' and b.type='char' and b.length=1
;
quit;

data want;
   set mylib.template(obs=0) work.imported(drop=&droplist);
run;  
ccaudillo100
Obsidian | Level 7

doing

 

input(cats(dropme),32.)

in the Proc SQL code worked as well. Thank you!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 461 views
  • 0 likes
  • 2 in conversation