i FINALLY FOUND THE SOLUTION!!!
There are multiple aspects involved in this process so hopefully this will help whoever wants to use Excel files to import into SAS!
/*Assigning librefs*/ libname SASData "E:\DI_Project\Data\SAS"; libname WorkData "E:\DI_Project\Data\WorkData";
/*Excel LIBNAME REF FOR DATA IMPORT*/ libname Paid "E:\DI_Project\Data\RawData\Paidsearch2019.xlsx" header=yes /*Tells SAS variable names are in 1st row*/ dbsaslabel=none /*Removes labels*/ scantime=yes /*Tells SAS to read date & time variables as datetime*/ mixed=yes /*Tells SAS not to put missing values if a variable has both character & numeric data*/ stringDates=no; /*Tells SAS NOT to treat dates as text strings or character variables*/
/*VERIFY VARIABLE LENGTHS & TYPES*/ proc contents data=paid."jan19$"n; run; proc contents data=paid."feb19$"n; run; proc contents data=paid."mar19$"n; run; proc contents data=paid."apr19$"n; run; proc contents data=paid."may19$"n; run; proc contents data=paid."jun19$"n; run; proc contents data=paid."jul19$"n; run; proc contents data=paid."aug19$"n; run; proc contents data=paid."sep19$"n; run; proc contents data=paid."oct19$"n; run; proc contents data=paid."nov19$"n; run; proc contents data=paid."dec19$"n; run;
/*JANUARY IMPORT*/ /*Formatting January Date Variable*/ data jandate; /*Output file or File being written to*/ retain Date; /*Tells SAS to retain the date formatting from Excel (treat as a date)*/ format Date ddmmyy10.; /*Tells SAS to format date from date9. to ddmmyy10., but you can choose whatever format desired*/ set paid."Jan19$A1:U470"n; /*Input file or File being read from*/ run;
/*VERIFY DATE FORMAT CHANGE*/ proc contents data=jandate; run;
/*METHOD 1: RE-ASSIGNING VARIABLE LENGTHS & TYPES*/ /*#1 - Create macrovariable varlist containing a list of variable names from Jan*/ proc contents data=jandate noprint out=metaclass; run;
options nocenter nodate; title 'Meta Data for jandate'; proc print data=metaclass; var name type length varnum; run;
data _null_; length allvars $1000; retain allvars ''; set metaclass end=eof; allvars = trim(left(allvars))||''||left(name); if eof then call symput('varlist',allvars); run;
%put &varlist; /*Macrovariable is created!!!*/
/*#2 - MODIFY VARIABLE TYPE & LENGTH IN JANDATE*/
/*Essentially this is renaming the variables, creating new variables with the old names & inputting the data, so it's like you modified the types & lengths, when in reality you created new variables altogether.*/
data sasdata.jan19 (drop=v1-v21); /*Creating new permanent SAS dataset for merging later & renaming variables*/ retain &varlist; *<-- preserve variable order ; length *<-- define new types/lengths; Campaign_name $100 Campaign_ID $15 Network $15 Impressions Ave_pageviews Ave_timeonsite_sec Ave_frequency Clicks Conversions Channel Bounce_rate Interaction_rate CTR Conversion_rate Daily_budget Cost CPC CPA Conversion_Value ROAS 8; format *<-- recreate formats; Campaign_name $CHAR100. Campaign_ID $CHAR15. Network $CHAR15. Date ddmmyy10. Impressions Ave_pageviews Ave_timeonsite_sec Ave_frequency Clicks Conversions 10. Channel 1. Bounce_rate Interaction_rate CTR Conversion_rate 4.2 Daily_budget Cost CPC CPA Conversion_Value ROAS DOLLAR10.2; set jandate (rename=( Campaign_name=v1 Campaign_ID=v2 Date=v3 Network=v4 Channel=v5 Impressions=v6 Bounce_rate=v7 Ave_pageviews=v8 Ave_timeonsite_sec=v9 Ave_frequency=v10 Interaction_rate=v11 Daily_budget=v12 Clicks=v13 CTR=v14 Cost=v15 CPC=v16 Conversions=v17 Conversion_rate=v18 CPA=v19 Conversion_Value=v20 ROAS=v21));
Campaign_name = put(v1,char100.); *<-- redefine variable; Campaign_ID = put(v2,char15.); *<-- redefine variable; Date = v3; Network = put(v4,char15.); *<-- redefine variable; Channel = v5; Impressions = v6; Bounce_rate = v7; Ave_pageviews = v8; Ave_timeonsite_sec = v9; Ave_frequency = v10; Interaction_rate = v11; Daily_budget = v12; Clicks = v13; CTR = v14; Cost = v15; CPC = v16; Conversions = v17; Conversion_rate = v18; CPA = put(v19,8.); *<-- redefine variable; Conversion_Value = v20; ROAS = v21; run;
/*FEBRUARY IMPORT: REPEAT EVERYTHING EXCEPT THE CREATION OF THE MACRO VARIABLE*/ /*Formatting January Date Variable*/ data febdate; retain Date; format Date ddmmyy10.; set paid."feb19$A1:U374"n; run;
/*Verifying Date Format Change*/ proc contents data=febdate; run;
/*RE-ASSIGNING VARIABLE LENGTHS & TYPES*/ /* Modifying variable type and length of febdate */ data sasdata.feb19 (drop=v1-v21); /*Creating new permanent SAS dataset for merging later & renaming variables*/ retain &varlist; *<-- preserve variable order ; length *<-- define new types/lengths; Campaign_name $100 Campaign_ID $15 Network $15 Impressions Ave_pageviews Ave_timeonsite_sec Ave_frequency Clicks Conversions Channel Bounce_rate Interaction_rate CTR Conversion_rate Daily_budget Cost CPC CPA Conversion_Value ROAS 8; format *<-- recreate formats; Campaign_name $CHAR100. Campaign_ID $CHAR15. Network $CHAR15. Date ddmmyy10. Impressions Ave_pageviews Ave_timeonsite_sec Ave_frequency Clicks Conversions 10. Channel 1. Bounce_rate Interaction_rate CTR Conversion_rate 4.2 Daily_budget Cost CPC CPA Conversion_Value ROAS DOLLAR10.2; set febdate (rename=( Campaign_name=v1 Campaign_ID=v2 Date=v3 Network=v4 Channel=v5 Impressions=v6 Bounce_rate=v7 Ave_pageviews=v8 Ave_timeonsite_sec=v9 Ave_frequency=v10 Interaction_rate=v11 Daily_budget=v12 Clicks=v13 CTR=v14 Cost=v15 CPC=v16 Conversions=v17 Conversion_rate=v18 CPA=v19 Conversion_Value=v20 ROAS=v21));
Campaign_name = put(v1,char100.); *<-- redefine variable; Campaign_ID = put(v2,char15.); *<-- redefine variable; Date = v3; Network = put(v4,char15.); *<-- redefine variable; Channel = v5; Impressions = v6; Bounce_rate = v7; Ave_pageviews = v8; Ave_timeonsite_sec = v9; Ave_frequency = v10; Interaction_rate = v11; Daily_budget = v12; Clicks = v13; CTR = v14; Cost = v15; CPC = v16; Conversions = v17; Conversion_rate = v18; CPA = put(v19,8.); *<-- redefine variable; Conversion_Value = v20; ROAS = v21; run;
/*Verifying sasdata.feb19*/ proc contents data=sasdata.feb19; run;
/*MERGE DATASETS*/ data sasdata.all_paid; set sasdata.jan19 sasdata.feb19; run; /*DATA NOT TRUNCATED!!!! YAAAASSSSSSS :)*/
*****NOTE: Obviously, the reassignment of the variables types & lengths part could be turned into a macro ultimately, or do-loop, but since I do not have time for this project, I will not cover this in this post. Enjoy!*****
... View more
