About Jcorti

Jcorti · ‎02-24-2017

Hi Team, I want something very easy. I want to mantain the same order of the variables of this dataset. set output.&cons ; I tried with retain and using keep statement and this does not mantain the same order of the variable names. I read on the forums and they suggested to use Retain and Format options validvarname=any; data testa (drop=r1 r2 "Range Start"n "Range End"n) ; format month_var $char32. Range $char24. "Current Count"n comma15. "Current %"n percent12.2 "Previous Count"n comma15. "Previous %"n percent12.2 "% Change"n percent12.2 ; set output.&cons ; r1=put(input("Range Start"n,15.),comma12.); r2=put("Range End"n,comma15.); Range=CATX('_',r1,r2); ; run; NOTE: Variable 'Previous Count'n is uninitialized. getting this like Output: month_var Range Current Count Current % Previous Count Previous % % Change Previous Count M00_ACCT_BALANCE_AM ._0 313,313,113 36.16% . 33.97% 2.19% 2,323,333,131 M00_ACCT_BALANCE_AM 1_100 313,313,113 5.58% . 33.97% 2.19% 2,323,333,131 And I need this: month_var Range Current Count Current % Previous Count Previous % % Change M00_ACCT_BALANCE_AM ._0 313,313,113 36.16% 2,323,333,131 33.97% 2.19% M00_ACCT_BALANCE_AM 1_100 313,313,113 5.58% 2,323,333,131 33.97% 2.19% M00_ACCT_BALANCE_AM 101_500 313,313,113 12.15% 2,323,333,131 33.97% 2.19% My question are 2: 1. Why is reading twice the variable "Previous Count" ? One with expected output and another with missing values 2. Is there an easy way to mantain the same order of the variable names of my dataset ? set output.&cons ;

Jcorti · ‎02-24-2017

r1=put(input("Range Start"n,15.),comma12.); r2=put("Range End"n,comma15.); Range=CATX('_',r1,r2); Yes I used that function and It worked, thanks for the comment

Jcorti · ‎02-24-2017

Thanks Kurt, It worked 🙂

Jcorti · ‎02-24-2017

No , Actually I want to convert it to char value because I am concatenating with another variable.. I tried and variable needs to be in char format to concatenate with another variable. So I need to convert it from: var=100000 to new_var=100,000 -->> new_var in char format

Jcorti · ‎02-24-2017

Hi Team, How I can convert a numeric value using put and input to comma. var=100000; new_var=100,000; Thanks and regards, JC

Jcorti · ‎02-23-2017

Now It worked properly 🙂 Thank you both!! NOTE: The import data set has 25 observations and 7 variables. NOTE: WORK.BAL_M00_ACCT_BAL_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: The import data set has 25 observations and 7 variables. NOTE: WORK.CR_M00_ACCT_CR_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.02 seconds cpu time 0.00 seconds NOTE: The import data set has 25 observations and 7 variables. NOTE: WORK.PYM1_M00_ACT_PYM_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.02 seconds cpu time 0.01 seconds NOTE: The import data set has 25 observations and 7 variables. NOTE: WORK.PYM2_M00_ACCT_P_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds

Jcorti · ‎02-23-2017

ERROR: DBMS type EXCEL not valid for import. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds This is a big mess!!

Jcorti · ‎02-23-2017

It is reading the file but not the number columns and rows: proc import out = bal_&var1 datafile = '/TEST/DVR_V1_PROD_01182017.xlsx' dbms = xlsx replace; sheet = "&var1"; range ="&var1..$A1:G26"; ********************************************LOG************************************* MPRINT(IMPORT_LOOP): range ="M00_ACCT_BALANCE_AM.$A1:G26"; 6 The SAS System 07:50 Thursday, February 23, 2017 MPRINT(IMPORT_LOOP): getnames = yes; MPRINT(IMPORT_LOOP): run; NOTE: The import data set has 25 observations and 9 variables. NOTE: WORK.BAL_M00_ACCT_BAL_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds MPRINT(IMPORT_LOOP): sheet = "M00_ACCT_CR_LM_AM"; SYMBOLGEN: Macro variable VAR2 resolves to M00_ACCT_CREDIT_LIMIT_AM MPRINT(IMPORT_LOOP): range ="M00_ACCT_CR_LM_AM.$A1:G26"; MPRINT(IMPORT_LOOP): getnames = yes; MPRINT(IMPORT_LOOP): run; NOTE: The import data set has 52 observations and 10 variables. NOTE: WORK.CR_M00_ACCT_CR_LM_AM data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds

Jcorti · ‎02-23-2017

Thanks, Let me try!!

Jcorti · ‎02-23-2017

Hi All, It ia a big mess import Excel files to SAS data sets. When I am selecting a range, I am selecting the range that contains 26 rows and 8 columns shown here --> $A1:G26 options validvarname=any; %let var1=M&i._ACCT_BALANCE_AM; proc import out = bal_&var1 datafile = '/TEST/DVR/DVR_V1_PROD_01182017.xlsx' dbms = xlsx replace; sheet = "&var1"; range ="&var1.$A1:G26"; getnames = yes; But the log is telling me other thing: It is reading 9 variables NOTE: The import data set has 25 observations and 9 variables. NOTE: WORK.BAL_M00_ACCT_BALANCE_AM data set was successfully created. Here is another example with another variable that I have defined in the same way: NOTE: The import data set has 52 observations and 10 variables. NOTE: WORK.CR_M00_ACCT_CREDIT_LIMIT_AM data set was successfully created. I have 2 questions: 1.- Why is not reading 8 columns and 26 rows defined on my RANGE statement? 2.- How can I fix this issue? Thanks in advance, JC

Jcorti · ‎02-21-2017

Hi again, Do you know if I can reference more variables here?? Asking because I need to cosolidate into: data allMyData; length fromSheet $32; set testv1_: indsname=name; fromSheet = cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM"); run; more macrovariables highlighted in black below: %do i1=0 %to 24; %let i = %sysfunc(putn(&i1,z2.)); %let cons=consolidated; %let var1=M&i._ACCT_BALANCE_AM; %let var2=M&i._ACCT_CREDIT_LIMIT_AM; %let var3=M&i._ACTUAL_PYMT_AM; %let var4=M&i._ACCT_PAYMENT_AM; Thanks a lot in advance, JC

Jcorti · ‎02-21-2017

Yes, I mean basically about **Ranges** I have to import 150 spreadsheet from one excel file This is an example of the names of the excel sheets: M00_ACCT_BAL_AM to M24_ACCT_BAL_AM ( 25 workbooks) M00_ACCT_CRED_LIM_AM to M24_ACCT_CRED_LIM_AM ( 25 workbooks) .. .. .. .. so on ... and total number is 150 Thanks

Jcorti · ‎02-21-2017

I was planning to do different with **proc import**, Actually I have 6 arrays on my excel file. So my assumption was weather I can do the **proc import** with 2 arrays in only one import I would be able to do it for 6 arrays I thought this part could be referenced in only one macro variable: %let bsheet="M&i._ACCT_BAL_AM"; /* Not Working */ %let csheet="M&i_ACCT_CR_AM"; /* Not Working */ It seems like it will be very tough make it in only **one import**, so the best solution should be generate 6 macro variables with each array??

Jcorti · ‎02-21-2017

Hi, I have my excel file in "xls" format. Is there a way that I can reference them like **Keyword parameters** ?? Example: %macro import_loop (sheet=,);

Jcorti · ‎02-21-2017

Hi Team, In the code shown below I want to reference multiple sheets and create only one table my excel file has multiple spreadsheets.. example: M00_ACCT_BAL_AM to M24_ACCT_BAL_AM (25 spreadsheets) M00_ACCT_CR_AM to M24_ACCT_CR_AM (25 spreadsheets) How Can I reference them on my macro? Thanks and regards, %macro import_loop; %let outobs=max; %do i1=0 %to 24; %let i = %sysfunc( putn(&i1,z2.)); %let sheet="M&i._ACCT_BAL_AM"; /* Not Working */ %let sheet="M&i_ACCT_CR_AM"; /* Not Working */ proc import out = testv1_&i (rename =('Current %'n=Current_per 'Previous %'n=Previous_per '% Change'n=Change_per)) datafile = '/test/DVR_V1_PROD_01182017.xls' dbms = xls replace ; sheet = "&sheet"; /*I want to reference both Arrays */ /*sheet = "M&i._ACCT_BAL_AMT"; this worked but I need to reference 2 different arrays*/ getnames = yes; run; data testv1_&i (drop='Run Date:'n I) ; set testv1_&i ; run; data allMyData (keep=SheetObject Range_Start Range_End Current_Count Previous_Count Current_per Previous_per Change_per); length SheetObject $32; set testv1_: indsname=name ; Rename 'Range Start'n=Range_Start 'Range End'n =Range_End 'Current Count'n=Current_Count 'Previous Count'n=Previous_Count ; SheetObject = cats("M", scan(name, 3, "._") , "_ACCT_BAL_AM"); run; %end; %mend import_loop;

Online Status	Offline
Date Last Visited	‎01-24-2018 12:29 PM

Re: Issue with macro variables %let

Issue with macro variables %let

Re: Output of Proc Compare get only differences

Output of Proc Compare get only differences

Re: Keeping only duplicates with proc sort with more than one variable

Keeping only duplicates with proc sort with more than one variable

Re: Concatenate with CATX

Re: Concatenate with CATX

Re: Concatenate with CATX

Concatenate with CATX

keep order of data set

Re: Convert a numeric value to comma

Re: Convert a numeric value to comma

Re: Convert a numeric value to comma

Convert a numeric value to comma

Re: Range of using Proc Import xlsx format

Re: Range of using Proc Import xlsx format

Re: Range of using Proc Import xlsx format

Re: Range of using Proc Import xlsx format

Range of using Proc Import xlsx format

Re: Union using Proc SQL - Macro Variables

Re: Proc Import Reference multiple Spreadsheets

Re: Proc Import Reference multiple Spreadsheets

Re: Proc Import Reference multiple Spreadsheets

Proc Import Reference multiple Spreadsheets