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

[Sleo007]

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! 😃

[Tom]

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;

[Sleo007]

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)?

[Tom]

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.

[Sleo007]

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;

[Tom]

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.

[Sleo007]

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.

[Tom]

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;

[Sleo007]

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?

[Tom]

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.

[Sleo007]

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.?

[Tom]

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. 

[Sleo007]

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?

[Tom]

Run PROC CONTENTS and check.

This SQL code will print any variable that has more than one TYPE or more than one LENGTH.

proc contents data=paid._all_ noprint out=contents; run;
proc sql ;
 select upcase(name) as uname,name,memname,type,length
  from contents 
  group by calculated uname
  having count(distinct type)>1 
      or count(distinct length)>1
  order by 1,2,3
  ;
quit;

[Sleo007]

This is the result: