I have a number of SAS programs that run over night and start with a series of x or %sysexec Windows shell commands that copy binary data files off of a server, invoke a program at the command line to export them into plain text, manipulate the plain text data files into fully-formatted SAS datasets, then grab related pdfs for each dataset and move them to a new location, using SAS to create a running table of where the PDFs land and which PDFs are related to which output SAS datasets. Imagine 3 or 4 nightly commands that loop simple commands like the following anywhere from 6 to 30 times each:     proc printto log = "C:\Users\Cghost\Logs"; run; %let server = \\network-share\; %let datafiles = datasetname; %sysexec xcopy /E "&server.&datafiles." "&localcopy.&datafiles." /Y; x """C:\Program Files (x86)\DataPackage\Bin\Conversion.exe"" ""&localcopy.&datafiles.\ExportData_&datafiles..conv""";     I log all this using proc printto, but obviously I don't see errors or outcomes (i.e., "File copied successfully") from the command shell, so this is leaving me unaware when a file is not copied correctly for some reason or when there's an error invoking the program used to convert the proprietary binary data files to plain text. So my question is, how can I grab this information back into SAS, and more specifically, what's the best way to do so? Is there a way to pull the result text from the command shell directly back into my SAS log so that I can see the results there? Or will I need to store it as a separate file (and if so, how can I append to the same daily log file for each time I invoke x or sysexec)? ... View more

Hi All,   I'm trying to come up with a program to randomly generate UserIDs containing the user's first name followed by a string of 4 digits. Making sure the 4-digit integers themselves are unique is easy enough based on this helpful previous community post, but now I have a situation where I need to verify that the integers are unique within name groups (i.e., all people named 'Fred') while I don't care if they're repeated between names. I figure I'll need to continue doing this with arrays, as in the linked post, but I can't quite figure out the syntax and logic, particularly when it comes to dynamically creating columns and setting default values for character variables. It seems there would be two ways to do this. The first would involve concatenating the digits with the first names right from the get go and then comparing them within a large character array to hunt for duplicates. The second would involve doing a (temporary?) numeric array with my full range of 4 digit values inside a do loop for each of the unique names I have. I'm not sure which would be the easiest or most efficient, but the latter seems like it would be easier to accomplish. Right now when we do this process (once or twice a week) there's a lot of manual de-duping and playing with ranges to get enough unique UserIds for our growing pool of users, so I'm hoping to eliminate that extra work.   Pretend my work.users file looks something like this before sorting, but with hundreds more rows: Id FirstName 1 John 2 John 3 Jane 4 David 5 David 6 David 7 Susan 8 Jane 9 Elizabeth 10 Leonard   This is what I have so far, but I'm not at all confident it's working correctly as the random isAssigned array is showing that isAssigned values are carrying between different groups (first names). I'd like to either cut this off and make sure the randomization is triggering specifically within groups of like first names (i.e., the three Davids) or just compare concatenated ids that would look like 'David5955' 'David1497' 'David8528'.     proc sql; select count(distinct FirstName) into: names from work.users; select max(value) into: recur from (select count(FirstName) as value from work.users group by FirstName); quit; proc sort data=work.users; by FirstName; run; data logtest; set work.users (keep = Id FirstName); by FirstName; array n[&recur.] n1-n%sysfunc(trim(&recur.)); call streaminit(1); array isAssigned[1:9999] (9999*0); do while (1); newId = (1000 + ceil((1+9999-1000)*rand("uniform"))); if isAssigned[newId] then continue; isAssigned[newId] = 1; leave; end; output; run;   ... View more

I have a number of different tables containing information on college students, such as their GPA at the end of each year, their major each year, amounts of different kinds of financial aid they took, cumulative credit hours, etc. Right now this exists in separate tables by topic (major and credits, financial aid, background info, etc.), with one row for each academic year.   I'd like to transpose every variable in some of these tables so that instead of having anywhere from 1-8 rows per student in each table (depending on how long they were in school), we have a set of columns for each academic year 1 through 8.    So, for example, the data I'm starting with looks like this:     StudentID AcademicYear CumGPA EdLevel Major AidAmount 1000 1 3.20 Freshman BasketWeaving $1,800 1002 1 2.77 Freshman SpaceAccounting $3,600 1002 2 2.93 Sophomore SpaceAccounting $933 1002 3 3.11 Junior SpaceAccounting $0 1002 4 3.03 Senior SpaceAccounting $1,200 1003 3 2.46 Junior Journalism $645 1003 4 2.75 Senior Journalism $4,300 1004 1 3.30 Freshman CryptoZoology $10,500 1004 2 2.73 Sophomore CryptoZoology $4,000 1004 3 2.34 Sophomore Biology $1,175     I want my output to look like this:     StudentID CumGPA_1 EdLevel_1 Major_1 AidAmount_1 CumGPA_2 EdLevel_2 Major_2 AidAmount_2 CumGPA_3 EdLevel_3 Major_3 AidAmount_3 CumGPA_4 EdLevel_4 Major_4 AidAmount_4 1000 3.20 Freshman BasketWeaving $1,800 1002 2.77 Freshman SpaceAccounting $3,600 2.93 Sophomore SpaceAccounting $933 3.11 Junior SpaceAccounting $0 3.03 Senior SpaceAccounting $1,200 1003 2.46 Junior Journalism $645 2.75 Senior Journalism $4,300 1004 3.30 Freshman CryptoZoology $10,500 2.73 Sophomore CryptoZoology $4,000 2.34 Sophomore Biology $1,175   The only way I can think of to do this with proc transpose involves rotating each of the above variables into new datasets individually (by StudentID with id AcademicYear), and then merging them all afterwards. I don't find that to be very practical given that I would have to do this for dozens of variables, and that we're expecting to receive additional data later in the year that will require everything to be done a second time.   I presume there are ways to do this with arrays in a datastep, but the examples of array code I have found are usually making the opposite transformation (from wide to long), and don't explain the steps very welll. Would someone be able to help me with this, or point me to resources that outline the steps and functions that I would need to do this in a very concice way?   ... View more

I have 7 similar files (different years of a survey) that I'm working to clean up so I can merge them. Around 80% of the columns/variables present in each file are common across all files. All of the files are in STC format which all means they have their own formats catalogues. However, these files were originally assembled by different people, and so there's a ton of minor discrepancies I need to work out between the files before they'll be clean enough to merge. This includes some differences in the user defined format labeling that I will need to resolve, as well as a number of variables that are incorrectly formatted as character rather than numeric variables in some of the files.   So I need to figure out where the labeling and type discrepancies exist between the 7 files, and bring them all up to standard before I try and merge them. I've got some techniques worked out for this, but I'm wondering if they're really the most efficient.   My questions are as follows: 1. What's the easiest way to bring all 7 of my data files into a common library without the formats catalogue being overwritten by each new STC file I import? Is there a way to declare a different formats catalogue for each different dataset within the same library? Currently I'm using a proc format cntlout statement after each proc cimport to move the formats for each of the datasets into their own dataset, but this seems kind of clunky and unlinks the formats from the data itself.   2. What's the easiest way to compare formats catalogs across the seven files to see where the labeling discrepancies are present? Right now I'm using a very convoluted proc sql query that grabs all the variables present in all of the files into a table, and then joins on all 7 of my cntlout format datasets where the format name and value are the same but the label is different. I'm not convinced it's working correctly, and it also can't really help me when a variable should be numeric type in all files but is numeric in some and character in others (causing the formats to have different names). If needed, I can provide my Proc SQL code for this.   3. Using Proc SQL and dictionary.columns, I was able to generate a table showing me the variables that have mismatched types between the files. I'm noticing in some years, the survey actually used free text responses instead of numeric coding, while in most other cases of mismatch, the person who put the original files together just accidentally left numeric columns as character type. So what I need is a quick way to scan through each of the columns with type mismatches, and determine whether they actually have any non-numeric characters present or whether they can be recoded to numeric without worry. I know there's an ANYALPHA function, but it seems intended for a case-by-case basis, while I'd like to find a way to put it into a datastep so that SAS can determine whether or not a variable has any alphabetical characters in it and either alter it to a numeric format or append "_text" to its column name.   For the top two questions I'm looking more for general guidance of "that's how you'd do it" or "you should look into this procedure/function instead", while for the third I'm looking for a little more of a direct technical response.   I'm still fairly new to SAS so I appreciate the help. I understand that these questions may be somewhat abstract on their own, so I'm happy to provide more detail upon request.      ... View more