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

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

azfvftrbhtbetbab — Fri, 08 Apr 2016 16:01:32 GMT

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

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

Reeza — Fri, 08 Apr 2016 16:06:19 GMT

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?

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

ballardw — Fri, 08 Apr 2016 16:06:33 GMT

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

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

azfvftrbhtbetbab — Fri, 08 Apr 2016 18:24:58 GMT

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

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

azfvftrbhtbetbab — Fri, 08 Apr 2016 18:28:35 GMT

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.

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

ballardw — Fri, 08 Apr 2016 19:30:40 GMT

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.

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

azfvftrbhtbetbab — Fri, 08 Apr 2016 20:40:56 GMT

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

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

Reeza — Fri, 08 Apr 2016 20:44:20 GMT

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.

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

ballardw — Mon, 11 Apr 2016 15:21:06 GMT

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.

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

Reeza — Fri, 08 Apr 2016 21:41:54 GMT

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

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

azfvftrbhtbetbab — Sat, 09 Apr 2016 02:01:35 GMT

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.

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

Markus_Ko — Mon, 26 Sep 2016 12:04:06 GMT

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