SAS Support Communities

Re-run the set up steps in the SAS Programming 1 essentials course. If you go back to the start of the course you should find the set up instructions. Make sure to read the log and see if the program runs successfully, if it doesn't then post the log here. ... View more

Sort by ID and DESCENDING type. Then if there's any 1's it will be at the top and you can decide where to output the record.   data A; input id date :yymmdd10. type; format date yymmdd10.; datalines; 1 2020-01-01 0 1 2018-07-06 0 2 2015-02-15 1 3 2020-02-01 0 3 2021-07-05 1 3 2021-08-09 0 4 2020-01-30 0 5 2018-10-10 1 5 2019-10-11 1 ; RUN; proc sort data=a; by id descending type; run; data zerotype onetype; set a; by id descending type; retain output_type; length output_type $8.; if first.id and type=1 then output_type='one'; else if first.id and type=0 then output_type='zero'; if output_type = 'zero' then output zerotype; if output_type = 'one' then output onetype; drop output_type; run; proc sort data=zerotype; by id date; run; proc sort data=onetype; by id date; run; @sasgorilla wrote: I have a data set that has IDs, dates, (other variables excluded below), and a "type" variable: data A; input id date :yymmdd10. type; format date yymmdd10.; datalines; 1 2020-01-01 0 1 2018-07-06 0 2 2015-02-15 1 3 2020-02-01 0 3 2021-07-05 1 3 2021-08-09 0 4 2020-01-30 0 5 2018-10-10 1 5 2019-10-11 1 ; RUN; If an ID ONLYhas a type value of "0", I want those id's observations to go to one dataset. If an id EVER has values of "1" I want those observations to go to another data set. See below:  data zerotype; input id date :yymmdd10. type; format date yymmdd10.; datalines; 1 2020-01-01 0 1 2018-07-06 0 4 2020-01-30 0 ; run; data onetype; input id date :yymmdd10. type; format date yymmdd10.; datalines; 2 2015-02-15 1 3 2020-02-01 0 3 2021-07-05 1 3 2021-08-09 0 5 2018-10-10 1 5 2019-10-11 1 ; run; How could I do this in a datastep?  Thank you!   ... View more

Your have data set has 4 records, but your want data sets have 5 records.   Are you sure for want2, the last one should be 5, not 6? If it is 6, I think this mostly works.    data want; set have; timestores = intck('weekday', date1, date2); *if start on weekend, move to first weekday; if weekday(date1) in (1,7) then date_start = intnx('weekday', date1, 1, 'b'); else date_start = date1; format date_start yymmdd10.; *second calculation; timestores2 = intck('day', date_start, date2); run; @sasgorilla wrote: You are right, sorry for not including code for clarity. Please see below.  data have; input date1 :yymmdd10. date2 :yymmdd10.; format date1 yymmdd10. date2 yymmdd10.; datalines; 2024-11-02 2024-11-04 2024-11-04 2024-11-04 2024-11-05 2024-11-07 2024-11-07 2024-11-11 ; RUN; data want1 /*don't count only 1st weekend if date1 on 1st weekend*/; input date1 :yymmdd10. date2 :yymmdd10. timetores; format date1 yymmdd10. date2 yymmdd10.; datalines; 2024-11-02 2024-11-04 0 2024-11-04 2024-11-04 0 2024-11-05 2024-11-07 2 2024-11-07 2024-11-11 4 2024-11-09 2024-11-18 7 ; RUN; data want2 /*never count weekend*/; input date1 :yymmdd10. date2 :yymmdd10. timetores; format date1 yymmdd10. date2 yymmdd10.; datalines; 2024-11-02 2024-11-04 0 2024-11-04 2024-11-04 0 2024-11-05 2024-11-07 2 2024-11-07 2024-11-11 2 2024-11-09 2024-11-18 5 ; RUN;   ... View more

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md   Here's a tutorial on this, I wrote a while back 🙂 ... View more

PROC TRANSPOSE will get you close, the names aren't quite a match.   proc transpose data=have prefix=threshold_risk_; by zip; id risk; value threshold; run; Ensure your data is sorted by ZIP and RISK prior to transpose to have the data in the desired order.   @Cal_Hottie wrote: I need to transpose my data with set up as follows:    Original table zip  risk  threshold 90001  1.0  18.22 90001  1.5  20.55 90001 2.0  22.01 90001 2.5 25.95 ... 96962 1.0 19.24 96962 1.5 21.45 96962 2.0 23.85 96962 2.5 25.47   Desired table: zip  threshold_risk_1.0  threshold_risk_1.5  threshold_risk_2.0  threshold_risk_2.5 90001  18.22  20.55  22.01  25.95 ... 96962  19.24  21.45  23.85  25.47   ... View more

@PaigeMiller wrote: @bhr-q wrote: sorry, my question wasn't clear Ok, @bhr-q , apparently you have received a correct answer, but I have no idea what question it is answering. Could you make that clear? Based on the answer and re-reading the question, OP wants to INCLUDE missing values in the mean function - so he wants to have them considered as 0, but the default behaviour is to exclude these values. He wants them included in the calculations.  ... View more

This is essentially the same answer as PROC SORT, which doesn't answer the problem (later defined in the thread), how to detect a data set that is in order but hasn't been explicitly sorted to have the sort flag in place. Otherwise, this is definitely a correct solution. ... View more

I know. If you need to sort after you check if the data is sorted and the answer is no, then you may as well sort in the first place. ... View more

@akhq wrote: Then I also know that I could read the CSV using infile, but the issue is that I need to retain the original metadata for the table, and those are all lost in the process. And for example I could have in original data a value of "0000005151" CHAR and then proc import reads it in as 51515 NUM and I have no metadata in the original table to convert that back to the as stated value with correct format of zX. .    If you need to maintain the metadata you should be writing the reading and writing the file using data steps not proc import/export. Export is less problematic.    But you're also involving Excel which is using it's own "proc import" to read the file.     You're best bet is to read it in using a data step and write it using a data step, which can be automated in SAS.    When you mention localization, is that to EU once, or do you need the ability to switch locales depending on the users?   @akhq wrote: I am losing my mind over proc import... I would need to export a sas dataset into a CSV (or xls/x doesn't matter), then I would modify the file and import it back to sas. The issue is that if I add a row with new values then everything looks ok in excel, but proc import fails to import rows or even columns without any error/warning... I have tried to convert all the date variables and numeric variables into a format of 'value so that excel would consider them character, however what happens is that if I add new values then the ' will be disappearing automatically and the raw csv will also be saved without the '. I hacked this so that when filling in excel I then use '' to get single ' into the csv, and that works, however the import still fails. I have attached an testfile (last row is added after the export) and a code I have used is the following: proc import file="&path./testdata_tomod.csv" dbms=csv replace out=import_testing; delimiter=";";run; Then I also know that I could read the CSV using infile, but the issue is that I need to retain the original metadata for the table, and those are all lost in the process. And for example I could have in original data a value of "0000005151" CHAR and then proc import reads it in as 51515 NUM and I have no metadata in the original table to convert that back to the as stated value with correct format of zX. .  So a) what the heck is happening there? b) is there any way to get dates and localisation issues handled without doing extensive manipulation and back and forth labelling etc etc.? (In EU localisation to excel uses "," as decimal delimiter instead of ".")   ... View more

From an efficiency standpoint, which is faster - sorting ahead of time or checking with the data step. My intuition says sort since the procedures are optimized while a data step has to run through all records (unless the out of order data is early in the dataset) ... View more

AFAIK, there isn't a way to know this without scanning the whole data set. So if a sort is important or required you need to sort the data so that there is an explicit sort indicator created on the dataset or so that you can trust it. If the data is already sorted, the sort procedure will be efficient.   ... View more

You can run proc contents and check the SORTED output or use the SORT* variables in sashelp.vtable for a more dynamic approach if you want to check via code. ... View more

It is not needed, but if you have a year AND date variable it ensures that the data is sorted correctly within the year to get the correct running sum. Otherwise, there could be a sum that's reset by year, but out of order by day. This isn't stated in the question but best practice based on experience.   @Season wrote: Your code is generally correct except for an issue that could be improved: the variable date is not needed in the BY statement.   ... View more