topic Am I importing the Excel workbook properly or are values being truncated? in SAS Programming

Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 03:35:25 GMT

Hello,

I am relatively new to SAS and am wondering whether values are being truncated when I import. I have read quite a bit on importing, length statement, informat, and format statement, as well as importing an Excel workbook using proc import, infile statement, etc. My ultimate goal is to merge paid search, organic search, Google Analytics, and Facebook analytics data sets (4) which I downloaded through Supermetrics for Google sheets for funnel conversion insight. The data set I am stuck on (#1 unfortunately) is Google paid search data, with tabs as months. All my data sets are Excel with tabs as months, so if I figure this out, it should hopefully work for the rest!

My goal was to import the Excel workbook and merge the months. The best solution I found for this was:

*Impporting Paidsearch data & creating SAS dataset;
%macro pim(sheet); /*Creating macro variable for proc import step*/

proc import out=paid_temp /*Creating work.paid_temp data set*/

datafile='E:\DI_Project\Data\RawData\Paidsearch2019.xlsx' /*Importing Excel Workbook*/

dbms=xlsx /*Defining DBMS*/

replace; /*Replace = will allow you to replace the exported data set if you re-run the code*/

sheet="&sheet"; /*referring to macro variable for sheet names*/

getnames=yes;

run;

%mend piim; /*Defining macro variables for each individual sheet in workbook*/

%pim(Jan19);

%pim(Feb19);

%pim(Mar19);

%pim(Apr19);

%pim(May19);

%pim(Jun19);

%pim(Jul19);

%pim(Aug19);

%pim(Sep19);

%pim(Oct19);

%pim(Nov19);

%pim(Dec19);

The only problem is that mainly the variable Campaign_Name is a text string with varying lengths (36-88) depending on the month, so I am wondering:

a) if there is some way to standardize (or change) the variable length (and type) while importing into SAS to avoid truncation and additional missing values due to values that are shorter or not the same length?

b) how to treat the many missing values for the numeric variables in the data set (ex: not sure if I should leave them as missing or replace them with zeros)?

The majority of the other variables are numeric, 8 bytes, BEST12. The variables are marketing metrics (ex: CPC, CTR, Conversion value, etc.).

I have to do a report for Friday so any help is VERY MUCH appreciated! 😃

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 03:39:28 GMT

One of the problems with using a spreadsheet for data entry. There isn't an easy way to tell SAS to ignore what your Excel sheet has. But you should be able to adjust to it pretty easily if the variable names (or as Excel sees them column headers) are the same in each sheet.

In fact you should be able to use the XLSX libname and so that you don't need the macro.

When combining the datasets just define the length of the variables BEFORE the SET statement to avoid truncation. Make sure to remove the formats that SAS will mistakenly attach to the character variables. If you have variable that is 40 characters long but use the $30. format with it then you will only see the first 30 characters of the values.

libname paid xlsx 'E:\DI_Project\Data\RawData\Paidsearch2019.xlsx'  access=readonly;
data all ;
  length Campaign_Name $40 ;
  set paid.Jan19 paid.Feb19 paid.Mar19 paid.Apr19 paid.May19 paid.Jun19
      paid.Jul19 paid.Aug19 paid.Sep19 paid.Oct19 paid.Nov19 paid.dec19
  ;
  format _character_ ;
run;

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 03:43:09 GMT

Hi Tom,

Thanks for the quick reply! 🙂 Yes, the variable names are all the same & I purposely changed them in Excel to shorten the time to find a proper solution. I will shortly try your code. Just to be sure, am I right in assuming that _character_ is to be replaced by the variable name (in my case Campaign_Name)?

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 05:43:49 GMT

No need to change it. _CHARACTER_ is an special variable list. It will resolve to every character variable in the current data step (at least that the compiler has seen at the point it compiles that statement). When you use a format statement with a variable list that is not followed by any format specification that tells SAS to attach no format to the variables.
You could attach formats to your character variables in that data step to override the one's that the XSLX engine will generate for each sheet/dataset. But attaching $xx. formats to character variables is not needed since SAS already knows how to display character values and so you don't have to give special instructions for how to display character variables (which is what a format is). Plus as I said before $xx. formats attached to character varaibles can actually cause harm when the format width is different than the variable's length.

Re: Am I importing the Excel workbook properly or are values being truncated?

ballardw — Wed, 18 Dec 2019 16:26:50 GMT

@Sleo007 wrote:

Hello,

I am relatively new to SAS and am wondering whether values are being truncated when I import. I have read quite a bit on importing, length statement, informat, and format statement, as well as importing an Excel workbook using proc import, infile statement, etc. My ultimate goal is to merge paid search, organic search, Google Analytics, and Facebook analytics data sets (4) which I downloaded through Supermetrics for Google sheets for funnel conversion insight. The data set I am stuck on (#1 unfortunately) is Google paid search data, with tabs as months. All my data sets are Excel with tabs as months, so if I figure this out, it should hopefully work for the rest!

The only problem is that mainly the variable Campaign_Name is a text string with varying lengths (36-88) depending on the month, so I am wondering:

a) if there is some way to standardize (or change) the variable length (and type) while importing into SAS to avoid truncation and additional missing values due to values that are shorter or not the same length?

b) how to treat the many missing values for the numeric variables in the data set (ex: not sure if I should leave them as missing or replace them with zeros)?

The majority of the other variables are numeric, 8 bytes, BEST12. The variables are marketing metrics (ex: CPC, CTR, Conversion value, etc.).

I have to do a report for Friday so any help is VERY MUCH appreciated! 😃

Proc Import "guesses" the length of variables for each individual proc call. The length and type of variables by default are set by examining the first 20 rows of data. So Proc Import is often a poor choice for production work involving multiple files with changing data as you mention your Campaign_name does.

Have you checked to see if any of your (possibly) sparse variables have changed type from character to numeric or vice versa between imported sets?

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 18:43:46 GMT

Hi Ballard,

Thanks for your reply! Yes, while trying Tom's code above, I actually received a notification ERROR: Variable Unique_Cookies has been defined as both character and numeric. Therefore, I am looking to force variable length and type while avoiding the chance of truncation.

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 19:10:50 GMT

Hi Tom,
So to simplify my life I deleted the Unique_Cookies variable & ran your code, but it seems to have only imported the January sheet for some reason. All other sheets are truncated. Any idea why?
Thanks.

The Log shows the following:
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='E:\DI_Project\FINAL.egp';
6 %LET _CLIENTPROJECTPATHHOST='JM-SCBTM11';
7 %LET _CLIENTPROJECTNAME='FINAL.egp';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 data all_paid ;
27 length Campaign_Name $100;
28 set paid.Jan19 paid.Feb19 paid.Mar19 paid.Apr19 paid.May19 paid.Jun19
29 paid.Jul19 paid.Aug19 paid.Sep19 paid.Oct19 paid.Nov19 paid.dec19;
30 format _character_;
31 run;

NOTE: The import data set has 1981 observations and 21 variables.
NOTE: The import data set has 3859 observations and 21 variables.
NOTE: The import data set has 5479 observations and 21 variables.
NOTE: The import data set has 6575 observations and 21 variables.
NOTE: The import data set has 6090 observations and 21 variables.
NOTE: The import data set has 5549 observations and 21 variables.
NOTE: The import data set has 5506 observations and 21 variables.
NOTE: The import data set has 6320 observations and 21 variables.
NOTE: The import data set has 9969 observations and 21 variables.
NOTE: The import data set has 5057 observations and 21 variables.
NOTE: The import data set has 4174 observations and 21 variables.
NOTE: The import data set has 1985 observations and 21 variables.
NOTE: There were 1981 observations read from the data set PAID.Jan19.
NOTE: There were 3859 observations read from the data set PAID.Feb19.
NOTE: There were 5479 observations read from the data set PAID.Mar19.
NOTE: There were 6575 observations read from the data set PAID.Apr19.
NOTE: There were 6090 observations read from the data set PAID.May19.
NOTE: There were 5549 observations read from the data set PAID.Jun19.
NOTE: There were 5506 observations read from the data set PAID.Jul19.
NOTE: There were 6320 observations read from the data set PAID.Aug19.
NOTE: There were 9969 observations read from the data set PAID.Sep19.
NOTE: There were 5057 observations read from the data set PAID.Oct19.
NOTE: There were 4174 observations read from the data set PAID.Nov19.
NOTE: There were 1985 observations read from the data set PAID.dec19.
NOTE: The data set WORK.ALL_PAID has 62544 observations and 21 variables.
2 The SAS System 13:33 Wednesday, December 18, 2019

NOTE: DATA statement used (Total process time):
real time 7.32 seconds
cpu time 7.31 seconds

32
33 GOPTIONS NOACCESSIBLE;
34 %LET _CLIENTTASKLABEL=;
35 %LET _CLIENTPROCESSFLOWNAME=;
36 %LET _CLIENTPROJECTPATH=;
37 %LET _CLIENTPROJECTPATHHOST=;
38 %LET _CLIENTPROJECTNAME=;
39 %LET _SASPROGRAMFILE=;
40 %LET _SASPROGRAMFILEHOST=;
41
42 ;*';*";*/;quit;run;
43 ODS _ALL_ CLOSE;
44
45
46 QUIT; RUN;

Re: Am I importing the Excel workbook properly or are values being truncated?

ballardw — Wed, 18 Dec 2019 19:12:16 GMT

@Sleo007 wrote:

Hi Ballard,

Thanks for your reply! Yes, while trying Tom's code above, I actually received a notification ERROR: Variable Unique_Cookies has been defined as both character and numeric. Therefore, I am looking to force variable length and type while avoiding the chance of truncation.

That will likely involve something done with each tab of your spread sheet separately mapping the original variable(s) to standard new variables. Something like:

libname paid xlsx 'E:\DI_Project\Data\RawData\Paidsearch2019.xlsx'  access=readonly;
data work.jan19 ;
  length Campaign_Name $40 ;
  set paid.Jan19 ;
  format _character_ ;
  /* test type of variable to create new consistent variable*/
  /* this creates a new character variable*/
  length NewCookies $ 15;
  if vtype(Unique_cookies)='C'  then NewCookies=Unique_cookies;
  else NewCookies = put(Unique_cookies,best15.);
  Drop Unique_cookies;
run;

And then make the All data set by combining the work.Jan19 work.Feb19 etc. The above is just an example of the logic. You should know whether you want a numeric or character version and how to convert to the desired type. (Input instead of Put and an appropriate informat which likely should NOT include a specified length such as Input(Unique_cookies,F.) to avoid implied decimals ending up in your data unless you know they are wanted.

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 19:15:49 GMT

Why do you say that?
The NOTES clearly say it read only 1,981 observations from the JAN19 sheet and wrote a total of 62,544 observations.

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 19:20:26 GMT

Yes, but after all the January observations, the rest are all truncated. So I have 11 sheets which appear as missing values (like 62,544-1981 are all missing values). In reality, they are not actually missing.

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 19:30:01 GMT

That is a surprising result.

And if you read them separately and then combine them does the same thing happen?

data jan19; set paid.jan19; run;
data feb19; set paid.feb19; run;
data both; set jan19 feb19; run;

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 19:37:32 GMT

Yes, initially when trying your code, I received the following log Note:

WARNING: Multiple lengths were specified for the variable Campaign_name by input data set(s). This can cause truncation of data.
NOTE: There were 1981 observations read from the data set WORK.JAN19.
NOTE: There were 3859 observations read from the data set WORK.FEB19.
NOTE: The data set WORK.BOTH has 5840 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

when I placed a length statement after each data statement, that was not the case.

NOTE: There were 1981 observations read from the data set WORK.JAN19.
NOTE: There were 3859 observations read from the data set WORK.FEB19.
NOTE: The data set WORK.BOTH has 5840 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Perhaps I should be using a macro like the one I had in my original post or at the least a do loop with a macro? That would technically reiterate the process, initializing the length statement each time, no?

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 19:40:49 GMT

Truncation is different than totally missing values.
Also you cannot fix type mismatches as easily. You cannot combine two datasets that use the same name to mean different types of variables. If you are lucky those are just caused by one column being completely empty in one of the spreadsheets. In that case you could drop the offending column and not have to worry about fixing it.

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 19:48:25 GMT

So I just tried your code with the added length statement after the data statement & still the same results. Log shows:

26 data both; length Campaign_Name :$100.; set jan19 feb19; run;

WARNING: Multiple lengths were specified for the variable Campaign_name by input data set(s). This can cause truncation of data.
NOTE: There were 1981 observations read from the data set WORK.JAN19.
NOTE: There were 3859 observations read from the data set WORK.FEB19.
NOTE: The data set WORK.BOTH has 5840 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

So it's definitely a problem with the length. Should I be using an informat to read in the data from paid.jan19, paid.feb19, etc.?

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 19:59:54 GMT

I tried your suggestion with the Campaign_Name variable with this code:

libname paid xlsx 'E:\DI_Project\Data\RawData\Paidsearch2019.xlsx' access=readonly;

data test19 ;
length Campaign_Name $100;
set paid.Jan19;
format _character_ ;
length CName $100;
if vlength(Campaign_Name)=100.
then CName=Campaign_Name;
else CName = put(Campaign_Name,$CHAR100.);
Drop Campaign_Name;

run;

It seems to have worked. Any idea how I can make it a do loop/macro for the rest of the months to not have to repeat coding? Thanks Ballard! 🙂

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 20:08:09 GMT

So I just tried running your code for all data sheets, with the following log. Truncation still happening past January. 😞

25 GOPTIONS ACCESSIBLE;
26 data all_paid;
27 length Campaign_Name $100;
28 set Jan19 Feb19 Mar19 Apr19 May19 Jun19 Jul19 Aug19 Sep19 Oct19 Nov19 Dec19;
29 drop Campaign_Name;
30 format _character_;
31 run;

NOTE: Variable Campaign_Name is uninitialized.
NOTE: There were 1981 observations read from the data set WORK.JAN19.
NOTE: There were 3859 observations read from the data set WORK.FEB19.
NOTE: There were 5479 observations read from the data set WORK.MAR19.
NOTE: There were 6575 observations read from the data set WORK.APR19.
NOTE: There were 6090 observations read from the data set WORK.MAY19.
NOTE: There were 5549 observations read from the data set WORK.JUN19.
NOTE: There were 5506 observations read from the data set WORK.JUL19.
NOTE: There were 6320 observations read from the data set WORK.AUG19.
NOTE: There were 9969 observations read from the data set WORK.SEP19.
NOTE: There were 5057 observations read from the data set WORK.OCT19.
NOTE: There were 4174 observations read from the data set WORK.NOV19.
NOTE: There were 1985 observations read from the data set WORK.DEC19.
NOTE: The data set WORK.ALL_PAID has 62544 observations and 21 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 20:08:29 GMT

If you know the maximum length for each character variable then defining it before the SET statement will make sure no values are truncated. Just because you get the warning does not mean anything is actually truncated. But it might be that other variables are being truncated.

Re: Am I importing the Excel workbook properly or are values being truncated?

Tom — Wed, 18 Dec 2019 20:10:04 GMT

It does not look like your datasets actual have a variable with that name.

NOTE: Variable Campaign_Name is uninitialized.

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 20:10:47 GMT

Aside from the Network variable, which is CHAR15, all other variables are defined as numeric 8 bytes, BEST12. So how do I solve the truncation occurring because of potentially other variables if I do not know which variables are causing the issue?

Re: Am I importing the Excel workbook properly or are values being truncated?

Sleo007 — Wed, 18 Dec 2019 20:16:26 GMT

Okay, I just tried:

data all_paid;
length CName $100;
set Jan19 Feb19 Mar19 Apr19 May19 Jun19 Jul19 Aug19 Sep19 Oct19 Nov19 Dec19;
format _character_;
run;

and received no errors in the log, yet still nothing after January.