DATA Step, Macro, Functions and more

Updating Comment Field in New Report Based on Report From Last Month

Reply
Occasional Contributor
Posts: 6

Updating Comment Field in New Report Based on Report From Last Month

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

Super User
Posts: 17,849

Re: Updating Comment Field in New Report Based on Report From Last Month

So how does one pronounce your user name? Smiley Happy

 

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?

 

 

Super User
Posts: 10,514

Re: Updating Comment Field in New Report Based on Report From Last Month

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?)

Occasional Contributor
Posts: 6

Re: Updating Comment Field in New Report Based on Report From Last Month

The data gets entered into the report manually after it is exported to Excel. I'll be bringing it back into SAS with PROC IMPORT to do the comparison with the new report.
Occasional Contributor
Posts: 6

Re: Updating Comment Field in New Report Based on Report From Last Month

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

 

 

Super User
Posts: 10,514

Re: Updating Comment Field in New Report Based on Report From Last Month

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.

Super User
Posts: 17,849

Re: Updating Comment Field in New Report Based on Report From Last Month

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. 

Occasional Contributor
Posts: 6

Re: Updating Comment Field in New Report Based on Report From Last Month

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

Super User
Posts: 10,514

Re: Updating Comment Field in New Report Based on Report From Last Month

[ Edited ]

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.

Occasional Contributor
Posts: 6

Re: Updating Comment Field in New Report Based on Report From Last Month

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.

Super User
Posts: 17,849

Re: Updating Comment Field in New Report Based on Report From Last Month

In addition to the issues @ballardw has mentioned, long text fields can get truncated and in general not read correctly. 

New Contributor
Posts: 2

Re: Updating Comment Field in New Report Based on Report From Last Month

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:

apparo.solutions

Ask a Question
Discussion stats
  • 11 replies
  • 366 views
  • 0 likes
  • 4 in conversation