About sasprogramming

sasprogramming · ‎08-26-2021

I think I have just found the problem. It is the free text field in my data that seems to be the problem, most cell entries are like this: jennifer wants $10000 sales too low over the limit However there are a few entries that have an equals at the beginning, like so: =jennifer wants $10000 =sales too low When I went into the raw data and removed the equals sign, the data successfully loaded in. I don't understand why this is tho. I discovered this fix by trial and error pretty much.

sasprogramming · ‎08-26-2021

I am in the process of looking into the the files. If my files do use LF only, how do I deal with this?

sasprogramming · ‎08-26-2021

I have 10 different csv files that I am importing. 8 of them import perfectly however the other dont don't import, there are no apparent differences between the files. Attached is the log: NOTE: The infile DUMMY is: Filename=\peter\testing\sales_Dec20, RECFM=V,LRECL=32767,File Size (bytes)=1240186, Last Modified=15 June 2021 04:26:00, Create Time=26 August 2021 19:10:30 NOTE: 0 records were read from the infile DUMMY. I have been trying to work this out for a long time, but I can't seem to find whats wrong? When I open the files in excel they all look the same. Is there any way I can inspect the file or something like that? It is very strange that this is happening. I imported all the files into R with ease, but for some reason SAS doesn't like these two files... Any help would be appreciated, thanks.

sasprogramming · ‎08-25-2021

Thanks, I am trying to understand where I would put in my data load in code, is this right? %let inpath=C:\Users\peter; data files; length dref $8 fname $200 ; rc = filename(dref,"&inpath."); did = dopen(dref); if did then do; do i = 1 to dnum(did); fname = dread(did,i); if /* filter here */ then do; fname = catx("\","&inpath.",fname); output; end; end; rc = dclose(did); end; rc = filename(dref); keep fname; run; data want; set files; infile dummy filevar=fname end=done delimiter = ',' MISSOVER DSD TERMSTR=CRLF lrecl=32767 /* other options */; do while (not done); firstobs=2 ; informat ACCOUNT_NUMBER $32. ; informat FACILITY_CREATION_DATE anydtdtm40. ; format ACCOUNT_NUMBER $32. ; format FACILITY_CREATION_DATE datetime. ; input ACCOUNT_NUMBER $ FACILITY_CREATION_DATE ; if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ end; run;

sasprogramming · ‎08-25-2021

options compress=Yes; data salesdata_all; %let _EFIERR_ = 0; /* set the ERROR detection macro variable infile 'C:\Users\peter\salesdata_jun2020.csv' delimiter = ',' MISSOVER DSD TERMSTR=CRLF lrecl=32767 firstobs=2 ; informat ACCOUNT_NUMBER $32. ; informat FACILITY_CREATION_DATE anydtdtm40. ; format ACCOUNT_NUMBER $32. ; format FACILITY_CREATION_DATE datetime. ; input ACCOUNT_NUMBER $ FACILITY_CREATION_DATE ; if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ run; Above is my SAS code for loading in the jun2020 sales data (csv format) I have a lot of similar files that I would like to load in concatenate together. They are: salesdata_jun2020 salesdata_july2020 salesdata_aug-sep2020 salesdata_oct2020 etc... As you can see they aren't always the same file name (with the month changed). I am struggling to write a macro to import all of this data and then concatenate together, rather than doing it manually. Any help would be appreciated. Thanks

sasprogramming · ‎08-10-2021

Value is used in the rate calculation. The data i have posted is fine. Take the rows in January in which cat1 = 1 Date Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Value Jan-18 1 0 0 0 1 1 0 0 Jan-18 0 1 1 0 0 1 1 0 Jan-18 0 1 0 1 0 1 1 1 0 1 1 0 0 1 0 0 Jan-18 1 0 0 0 0 0 0 1 Jan-18 0 1 0 1 0 0 1 0 Jan-18 0 1 0 1 1 0 1 1 Jan-18 0 0 1 0 0 1 1 1 Jan-18 1 1 1 0 0 0 1 1 Jan-18 1 0 0 0 1 1 0 1 CA rate for cat1 Jan = sum of value = 3 divided by sum of cat = 4, therefore rate = 3/4

sasprogramming · ‎08-09-2021

I have data like below. Date Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Value Jan-18 1 0 0 0 1 1 0 0 Jan-18 0 1 1 0 0 1 1 0 Jan-18 0 1 0 1 0 1 1 1 0 1 1 0 0 1 0 0 Jan-18 1 0 0 0 0 0 0 1 Jan-18 0 1 0 1 0 0 1 0 Jan-18 0 1 0 1 1 0 1 1 Jan-18 0 0 1 0 0 1 1 1 Jan-18 1 1 1 0 0 0 1 1 Jan-18 1 0 0 0 1 1 0 1 Jan-18 0 0 0 1 0 1 1 1 Feb-18 1 1 1 0 1 0 1 0 Feb-18 0 1 0 1 0 0 0 0 Feb-18 1 0 1 0 0 1 0 1 Feb-18 0 0 1 0 1 1 0 1 Feb-18 1 1 0 0 1 0 1 1 Feb-18 1 0 0 1 0 0 1 1 Feb-18 0 1 1 1 0 1 0 0 Feb-18 1 1 0 1 1 1 1 0 Mar-18 0 1 0 1 1 0 0 0 Mar-18 1 1 0 1 0 1 0 0 Mar-18 1 1 1 0 0 1 0 1 Mar-18 0 1 0 0 0 1 1 0 Mar-18 1 1 1 0 1 1 0 1 Mar-18 1 1 1 1 1 0 1 1 Mar-18 1 0 0 1 0 1 0 0 Mar-18 1 1 0 0 0 0 0 0 Mar-18 1 1 1 1 1 1 0 1 Mar-18 0 1 0 1 1 1 1 1 Mar-18 0 1 1 0 0 0 0 0 Mar-18 0 1 1 1 0 0 0 0 Mar-18 0 1 1 1 1 1 1 0 Mar-18 0 0 1 1 1 1 1 0 Mar-18 1 0 0 1 0 1 1 1 I would like to create in sas the following summary table: Rate Cat 1 Rate Cat 2 Rate Cat 3 Rate Cat 4 Rate Cat 5 Rate Cat 6 Rate Cat 7 Jan 75% 50% 50% 75% 67% 57% 71% Feb 60% 20% 50% 25% 50% 50% 50% March 63% 42% 50% 40% 57% 50% 50% These are rates are calculated in excel using an average if. E.g for for jan Rate cat1: So the jan rate cat1 = the average of the `value` in January, when Cat 1 =1. I am looking to do the same thing as what I have done in excel, but in SAS

sasprogramming · ‎07-27-2021

I would like to create a new column called 'Indicator' which is equal to "NOT_BLANK" if 'field' is not blank and "BLANK" if 'field' is blank. How I would do this in excel is: How can I do this in SAS? Thanks. Current data: Field A001_B001 A001 C002 C002_A001 B002 Desired data: Field Indicator A001_B001 NOTBLANK A001 NOTBLANK BLANK C002 NOTBLANK C002_A001 NOTBLANK B002 NOTBLANK

sasprogramming · ‎07-21-2021

'LODGE_DATE' is now formatted yymmd7.

sasprogramming · ‎07-21-2021

Thanks, this works but I want it to be the last 12months regardless of what date is the last observation. Like the logic i am after is: keep rows for: (last.date - 12months)

sasprogramming · ‎07-21-2021

I have a dataset in sas which contains many columns. One of which is a date column called `LODGE_DATE`. The dataset contains about 3years worth of data and will contiue to grow. I only want to export the last 12months worth of data, how can I do this without choosing the actual months to include/exclude? 'LODGE_DATE' is formatted MONYY5. Thanks

sasprogramming · ‎07-21-2021

I tried this and it didn't work as intended, this was the result. proc sql; select DATE, CATEGORY, VALUE, PCTL(75, VALUE) AS Q3 from current group by DATE, CATEGORY order by DATE, CATEGORY; quit;

sasprogramming · ‎07-21-2021

What is the function within sql I could use to also create a Q1 (as 25% percentile) column? Thanks

sasprogramming · ‎07-21-2021

This works thanks! Just need the quit; at the end

sasprogramming · ‎07-20-2021

proc import file="C:/Users/ggqeo/Documents/My SAS Files/current.csv" out=current dbms=csv; run;

Online Status	Offline
Date Last Visited	‎03-31-2022 04:05 AM

Re: 0 records read from file

Re: 0 records read from file

0 records read from file

Re: macro for load in multiple files csv

macro for load in multiple files csv

Re: create an average if table

create an average if table

Creating if then column based on if cell is blank

Re: only keep last 12 months of data sas

Re: only keep last 12 months of data sas

Re: macro for load in multiple files csv

Re: SAS8; How to remove special characters from string?

Re: macro for load in multiple files csv

Re: macro for load in multiple files csv

Re: create an average if table

Re: calculate median by category and date

Re: 0 records read from file

Re: 0 records read from file

0 records read from file

Re: macro for load in multiple files csv

macro for load in multiple files csv

Re: create an average if table

create an average if table

Creating if then column based on if cell is blank

Re: only keep last 12 months of data sas

Re: only keep last 12 months of data sas

only keep last 12 months of data sas

Re: calculate median by category and date

Re: calculate median by category and date

Re: calculate median by category and date

Re: calculate median by category and date