Hi,   I have the following code:   DATA _null_; array files [1] $20 ('property.csv' 'lease.csv'); do i = 0 to 1; length filein 8 fileid 8; filein = fopen("/transport/default/run/incoming/files[i]",'I',1,'B'); end; RUN;   I am trying to loop through multiple CSVs saved on the server and copy them across to another folder (this is just part of the code). The issue, however, is that the code in filein evaluates directly to /transport/default/run/incoming/files[0]   How do I force it to evaluate "files[0]" to 'property.csv' and "files[1]" to 'lease.csv'?   Thank you!   Mike           ... View more

Hi,   I'm running SAS Visual Analytics 8.2.   I would like to do the following: Create a button bar with several categories Based on the selection in this button bar, change the "Rows" Data Role in a crosstab.   For example, imagine a button bar with two options, "State" and "City". If the user selects "State" then the table would look like this: State Cost NC 100 TX 200 MN 150 If the user selects "City", then the table look like this: City Cost Charlotte 40 Raleigh 140 Dallas 200 Memphis 150   So, basically, the row aggregation has changed based on the selection in the button bar.   I found this link: https://blogs.sas.com/content/sgf/2016/04/27/use-parameters-to-pick-your-metric-in-visual-analytics-reports/ which shows how to select a measure based on user input, but I can't work out how to select a row.   Let me know if I can make the above clearer.   Thanks,   Mike     ... View more

Hi,   I'm importing a .xlsx file into SAS Studio using PROC Import. Unfortunately, the names in Excel are strange, and I'm unable to reference them in SAS.   I've run the following code: proc sql; describe table lender_history_2018_02_28; quit; while yields the following results: create table LENDER_HISTORY_2018_02_28( bufsize=65536 ) ( 'CheckDate'n num format=MMDDYY10. label='CheckDate', 'CheckNumber'n char(5) format=$5. informat=$5. label='CheckNumber', C num format=BEST. label='C', D num format=BEST. label='D', 'Check Amount'n num format=NLMNY15.2 label='Check Amount', 'ServiceFees'n num format=NLMNY15.2 label='ServiceFees', 'Applied ToInterest'n num format=NLMNY15.2 label='Applied ToInterest', 'Applied ToPrincipal'n num format=NLMNY15.2 label='Applied ToPrincipal', 'Applied ToLate Charges'n num format=NLMNY15.2 label='Applied ToLate Charges', 'ChargesPrincipal'n num format=NLMNY15.2 label='ChargesPrincipal', 'ChargesInterest'n num format=NLMNY15.2 label='ChargesInterest', 'Applied ToPrepay Fee'n num format=NLMNY15.2 label='Applied ToPrepay Fee', 'OtherTaxable'n num format=NLMNY15.2 label='OtherTaxable', 'Applied ToNon-Taxable'n num format=NLMNY15.2 label='Applied ToNon-Taxable', 'OtherPayments'n num format=NLMNY15.2 label='OtherPayments', 'Applied ToTrust'n num format=NLMNY15.2 label='Applied ToTrust', 'LoanAccount'n char(10) format=$10. informat=$10. label='LoanAccount', 'Borrower Name'n char(45) format=$45. informat=$45. label='Borrower Name', Notes char(78) format=$78. informat=$78. label='Notes', 'Check Memo'n char(20) format=$20. informat=$20. label='Check Memo', 'Created By'n char(9) format=$9. informat=$9. label='Created By', 'Date Created'n num format=DATETIME16. label='Date Created', 'Date Last Updated'n num format=DATETIME16. label='Date Last Updated', 'PaymentCode'n char(6) format=$6. informat=$6. label='PaymentCode', 'PaymentDate Due'n num format=MMDDYY10. label='PaymentDate Due', 'PaymentRcvd Date'n num format=MMDDYY10. label='PaymentRcvd Date' );   75 quit; As you can see, the variable names (e.g. the  first one "'CheckDate'n") don't make sense, and I'm unable to reference them in later code. Unfortunately we receive these files from a vendor, so I can't change them. Any ideas on how I could fix this? I think I could do one of two things: 1) Rename the variables based on their column number (the ordering is always the same) 2) Rename the variables to their label, as these seem reasonable   Any ideas how I could do the above?   Many thanks,   Mike ... View more

Hi,   I have the following "oneapp.lease" dataset: property_id lease_id rent_amount1 rent_amount2 1 1 100 200 1 2 110 150 1 3 120 300 2 4 105 . And I would like to convert it to the following: property_id lease_id1 lease1_rent1 lease1_rent2 lease_id2 lease2_rent1 lease2_rent2 lease_id3 lease_rent1 lease_rent2 1 1 100 200 2 110 150 3 120 300 2 4 105 . . . . . . . In short, I'm trying to convert from narrow to wide, with one observation per property_id, creating variables lease1_rent1 to leaseN_rentM where there is a predefined max N and M (e.g. N= 10 and M = 3)   My code currently looks as follows: %let i = 1; %macro create_leases; DATA lease_wide; set oneapp.lease; by property_id; if first.property_id then &i. = 1; %do j = 1 %to &max_rent_changes.; lease&i._rent_amount&j. = rent_amount&j.; %end; %let i = &i. + 1; if last.property_id then output; RUN; %mend; %create_leases() I'm quite new to macros, and not sure why the above doesn't work?   Thanks,   Mike       ... View more

Hi,   I've seen a couple of similar threads, but haven't been able to use them to answer my question. I have the following dataset:   Address Year Account Amount Address1 2015 Account1 100 Address1 2015 Account2 200 Address1 2016 Account1 50 Address2 2015 Account1 150 Address2 2015 Account2 10 Address2 2016 Account1 50 Address2 2016 Account1 30 I would like to transpose this, such that each year-account combination is a variable, as below:   Address Year Account Amount _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2 Address1 2015 Account1 100 100 . . . Address1 2015 Account2 200 . 200 . . Address1 2016 Account1 50 . . 50 . Address2 2015 Account1 150 150 . . . Address2 2015 Account2 10 . 10 . . Address2 2016 Account1 50 . . 50 . Address2 2016 Account2 30 . . . 30 I would then like to aggregate such that each address is only shown once, as below: Address _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2 Address1 100 200 50 . Address2 150 10 50 30   Any thoughts on how I could do the above by dynamically creating the columns? I'm currently creating each column manually, but there are 15 accounts, and 5 years 🙂       ... View more

Hi,   I am trying to create a button bar which will be used to filter a chart. There are two measures that I would like to user to toggle between (Measure 1 and Measure 2), which I have assigned to a parameter, "Parameter".   I am now trying to create the calculated item, which, if Parameter = Measure 1 will give Aggregated 1, and if Parameter = Measure 2 will give Aggregated 2, as per the below: IF ('Parameter'p = 'Measure 1') RETURN 'Aggregated 1'n ELSE ( RETURN 'Aggregated 2'n)   However, I'm getting an error relating to 'Measure 1', reading "An element cannot be aggregated" And another error related to the if statement, reading "Operand can only be aggregated, but this operator does not support aggregation.   Any ideas what I'm doing wrong? Can I not use aggregated measures in an if statement like this?     ... View more

Hi, I have the code below, where I sort my dataset by my variables, and then use a data step to split my data into by groups, and create a count, resetting on the by group. Please see the code below: PROC SORT DATA = kpi.occ_rent out = occ_rent; by property_id date company0 metro; RUN; DATA ave_vac_by_pm_market; set occ_rent (rename = (company0 = company)); by property_id date company metro; /* Increment the vacancy days by one for each day vacant */ if active = 1 then vacancy_days + 1; if active = 0 or occupied0 = 1 then vacancy_days = 0; /* if last.metro then */ /* vacancy_days = 0; */ RUN; The above code, with the last two steps commented out, works just fine, except it doesn't split my count up every time there is a new "metro". However, if I add the final two steps in, all my "vacancy_days" read as zero. Its almost as though it thinks that each row is a new "metro", even though this isn't the case.   Any thoughts on why this may be?       ... View more