Hi all,
I have a report, actually multiple reports, where there are a great number of fields and one comment field at the end. This comment field is filled in manually when reviewing the each month. Right now, the report does not bring forward the comments from the previous month, and that's been a source of time wasted because certain records end up being reviewed repeatedly before realizing, "Oh, we reviewed that last month and the comment is there but is not in the new report". Is there a way I can have that one comment field brought in from the previous month easily? These reports are very wide, and each report has different variable names. I essentially want to merge the last months file with the current file on all fields EXCEPT the comment field, and I want to make it macro enabled to work with all the reports. Are there are good ideas on how to accomplish this?
-Paul
So how does one pronounce your user name?
It depends on your data structure. We don't have enough information right now to help.
I could suggest maintaining a separate table for comments that's then merged in at the end every month somehow?
Please provide some exampe of what you are actually doing. A couple of dummy tables of 1) start 2) after the comments are added, 3) what you expect the next round to look like.
Also you say "This comment field is filled in manually when reviewing the each month." So how does this get into your data system? (date entry? Where is the result stored?)
Data LastMonth;
infile datalines delimiter= '~' ;
input ABC: $100. DEF: $100. GHI: $100. JKL: $100. MNO: $100. PQR: $100. STU: $100. VWX: $100. YZ: $100. PM_Comments: $100. ;
datalines;
aasdfasdf~lajsdfoajds~qoiuqweoruqwer~poiuqwerpouqwer~skjdfg~kjkjdfd~iueiureiru~ieurieur~iusiuwe~Ok, this is the comment I need to bring forward
;
Data ThisMonth;
infile datalines delimiter= '~' ;
input ABC: $100. DEF: $100. GHI: $100. JKL: $100. MNO: $100. PQR: $100. STU: $100. VWX: $100. YZ: $100. PM_Comments: $100. ;
datalines;
aasdfasdf~lajsdfoajds~qoiuqweoruqwer~poiuqwerpouqwer~skjdfg~kjkjdfd~iueiureiru~ieurieur~iusiuwe~
aaasdfsdf~lajqwerqrewfoajds~qoqwerqoruqwer~poiqwerqwer~skqwefg~kjkwdfd~iueweeiru~ieurrieur~iutyuwe~
;
So if I had the data above, I'd want to create a report showing the 2 records from the ThisMonth dataset while also including the comment for the first record from the LastMonth dataset. In datalines, I want the following to come out:
Data NewReport;
infile datalines delimiter= '~' ;
input ABC: $100. DEF: $100. GHI: $100. JKL: $100. MNO: $100. PQR: $100. STU: $100. VWX: $100. YZ: $100. PM_Comments: $100. ;
datalines;
aasdfasdf~lajsdfoajds~qoiuqweoruqwer~poiuqwerpouqwer~skjdfg~kjkjdfd~iueiureiru~ieurieur~iusiuwe~Ok, this is the comment I need to bring forward
aaasdfsdf~lajqwerqrewfoajds~qoqwerqoruqwer~poiqwerqwer~skqwefg~kjkwdfd~iueweeiru~ieurrieur~iutyuwe~
;
Now keep in mind that I cannot be certain that the record in the LastMonth dataset will for sure be in the ThisMonth dataset, so I can't union them together and then eliminate the dups or something along those lines. I actually think I have to either merge by a bunch of BY variables or create an ID field with all the fields concatenated together to serve as a common ID. But I'd really like an easier way if it exists because I want to create a small macro to do this for multiple reports, all having different field names but having the commonly shared PM_Comments field that I want to bring in from last months report, if applicable.
-Paul
Have you tried reading one of the marked up Excel files?
Please show the results after you have done so.
I have a nasty suspicion that by itself will be a problem. The reason being that the "comment" value will likely be treated as one of the variables in a column above it.
If you're not merging by comments where do you want the comments to be included in the next months report?
In the first fields? A specific record? Do you have multiple comments or a single record for the whole report?
Sample data would be incredibly helpful here.
Does anyone see a potential issue with the solution I dreamed up below?
proc sql noprint;
select name into :columns separated by ' ' from dictionary.columns
where libname = 'WORK' and memname = 'LASTMONTH' and name ne 'PM_Comments'; *I want to have a macro variable to sort and merge by on all fields except the PM_Comments field;
quit;
proc sort
data = work.LastMonth;
by &columns; *Sort the columns according to the macro variable excluding the PM_Comments field.;
run;
proc sort
data = work.ThisMonth;
by &columns; *Sort the columns according to the macro variable excluding the PM_Comments field.;
run;
data new;
merge thismonth (in = a) lastmonth (in= b); *Take advantage of the fact that SAS will use the PM_Comments field of the last dataset in the MERGE statement. We always want the populated comments field from last month;
by &columns; *Merge on all columns excluding the PM_Comments field.;
if a = 1 then output; *Do not want records to show up from last month if they are not present in this months report;
run;
-Paul
The general approach looks like it would work.
I have a possible concern related to using Excel for the data entry. Since you are likely to Save the data after entering the "comment", Excel is known for being "helpful" and sometimes changing values under some conditions. So there is a chance that since you are using all of the variables read from Excel that you may get mismatches you shouldn't in the merge step.
What would be a better way to enter the comments so that we would be less likely to have mismatching text fields month over month? I definitely don't love Excel as a data storage tool, but everybody and their grandmother knows how to "use" it in a general sense. I'd be open to an alternative if it's reasonably painless to start using.
-Paul
Oh, and how can I change my username? I'm not wild about the auto-selected name I randomly received when logging in to post here.
In addition to the issues @ballardw has mentioned, long text fields can get truncated and in general not read correctly.
Hi,
How about managing the comments in relational DB tables?
This makes it far easier to further process them e.g. for workflows.
There is a tool that allows data entry (also comments) in reports:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: