09-28-2023
Scott_Mitchell
Quartz | Level 8
Member since
07-07-2013
- 328 Posts
- 39 Likes Given
- 1 Solutions
- 150 Likes Received
-
Latest posts by Scott_Mitchell
Subject Views Posted 1387 09-28-2023 03:34 AM 1156 07-28-2019 07:06 PM 1210 07-25-2019 08:12 PM 1608 07-09-2019 11:25 PM 1614 07-09-2019 10:09 PM 1665 07-08-2019 11:04 PM 1195 07-08-2019 10:25 PM 1312 06-26-2019 08:35 PM 1473 06-04-2019 03:04 AM 1486 06-03-2019 05:49 AM -
Activity Feed for Scott_Mitchell
- Tagged DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:36 AM
- Tagged DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:36 AM
- Tagged DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:35 AM
- Tagged DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:35 AM
- Tagged DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:35 AM
- Posted DI Studio SQL Merge Transformation User Guide on SAS Data Management. 09-28-2023 03:34 AM
- Got a Like for Re: Last 5 characters from string(irrespective of the length). 04-17-2020 02:04 AM
- Posted Re: Excel AMO - Deleting Stored Process Object and Retaining Values using VBA on Microsoft Integration with SAS. 07-28-2019 07:06 PM
- Posted Excel AMO - Deleting Stored Process Object and Retaining Values using VBA on Microsoft Integration with SAS. 07-25-2019 08:12 PM
- Tagged Excel AMO - Deleting Stored Process Object and Retaining Values using VBA on Microsoft Integration with SAS. 07-25-2019 08:12 PM
- Tagged Excel AMO - Deleting Stored Process Object and Retaining Values using VBA on Microsoft Integration with SAS. 07-25-2019 08:12 PM
- Tagged Excel AMO - Deleting Stored Process Object and Retaining Values using VBA on Microsoft Integration with SAS. 07-25-2019 08:12 PM
- Posted Re: SAS AMO - Passing Output Parameters To Excel on Microsoft Integration with SAS. 07-09-2019 11:25 PM
- Posted Re: SAS AMO - Passing Output Parameters To Excel on Microsoft Integration with SAS. 07-09-2019 10:09 PM
- Tagged SAS AMO - Passing Output Parameters To Excel on Microsoft Integration with SAS. 07-08-2019 11:07 PM
- Tagged SAS AMO - Passing Output Parameters To Excel on Microsoft Integration with SAS. 07-08-2019 11:07 PM
- Posted SAS AMO - Passing Output Parameters To Excel on Microsoft Integration with SAS. 07-08-2019 11:04 PM
- Posted Re: SAS AMO - Silent Execution of Stored Process on Microsoft Integration with SAS. 07-08-2019 10:25 PM
- Posted SAS AMO - Silent Execution of Stored Process on Microsoft Integration with SAS. 06-26-2019 08:35 PM
- Posted Re: PROC FCMP - Output Character Value Truncation on SAS Programming. 06-04-2019 03:04 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 11 1 1 1 -
My Liked Posts
Subject Likes Posted 2 07-29-2013 05:57 AM 1 09-18-2013 02:08 AM 3 07-15-2013 07:14 AM 3 09-12-2013 05:41 AM 3 09-12-2013 07:22 AM
07-09-2018
03:25 AM
Using the Input Statement ?? format modifier allows the value to be set to missing, suppresses the warning message in the log and sets the _ERROR_ automatic variable to 0. The same applies to the Input function.
data want;
input numval ??;
cards;
1
2
3
-
4
;
run;
... View more
06-19-2018
06:51 PM
Hi @Cynthia_sas,
As always you are a star. Your insights confirm my suspicions, but was hoping there was another way of approaching it. There are 3 reports per segment, so I was hoping to reduce the amount of code, alas that is unavoidable.
For completeness sake: Testing was simply used whilst developing the report, which was always going to be removed anyway. Unfortunately I can't remove the UNIFORM option as it is a requirement tables across pages be sized the same.
Thank you again for your reply.
Regards,
Scott
... View more
06-18-2018
11:44 PM
Good Afternoon All,
SAS Version : 9.4M4
OS: Windows Server 2012 R2
I haven't used PROC REPORT in quite some time so need a little refresher.
I have obtained results for the first report table I am looking to output (please see the below code - SYNTAX 1), but am having a few problems with adding subsequent tables.
SYNTAX 1
/*CREATE EXAMPLE DATA*/ DATA SHOES ;
SET SASHELP.SHOES (IN = A KEEP = PRODUCT SUBSIDIARY SALES)
SASHELP.SHOES (IN = B KEEP = PRODUCT SUBSIDIARY SALES);
IF B AND _N_ = 396 THEN ITEM = 0;
IF B THEN DO;
SALES = SALES *.15;
SUBSIDIARY = "London";
END;
ITEM + 1;
BOOTS = 1;
MEN_CASUAL = 1;
MEN_DRESS = 1;
RUN;
OPTIONS LEFTMARGIN = .5IN RIGHTMARGIN = .5IN ORIENTATION = LANDSCAPE CENTER MISSING="" NODATE NONUMBER;
ODS LISTING CLOSE;
ODS HTML CLOSE;
ODS PDF FILE= "&WORKPATH.\SHOES.PDF" UNIFORM;
PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
COLUMNS ITEM PRODUCT SUBSIDIARY SALES SALES = BOOTSSALES SALES = MENCASUALSALES SALES = MENDRESSSALES BOOTS MEN_CASUAL MEN_DRESS
BOOTS MEN_CASUAL MEN_DRESS;
DEFINE ITEM / GROUP "Item";
DEFINE PRODUCT / GROUP;
DEFINE SUBSIDIARY / GROUP;
DEFINE BOOTS / GROUP NOPRINT;
DEFINE MEN_CASUAL / GROUP NOPRINT;
DEFINE MEN_DRESS / GROUP NOPRINT;
DEFINE BOOTSSALES / SUM NOPRINT;
DEFINE MENCASUALSALES / SUM NOPRINT;
DEFINE MENDRESSSALES / SUM NOPRINT;
COMPUTE ITEM;
ITEM2 = ITEM;
IF MOD(ITEM2,2) THEN DO;
CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=#f2f2f2]");
END;
ENDCOMP;
COMPUTE SALES;
IF UPCASE(TRIM(LEFT(PRODUCT))) = "BOOT" THEN DO;
H_CNTBOOTS+1;
H_SALESBOOTSCASH+SALES.SUM;
END;
ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S CASUAL" THEN DO;
H_CNTMENCASUAL+1;
H_SALESMENCASUAL+SALES.SUM;
END;
ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S DRESS" THEN DO;
H_CNTMENDRESS+1;
H_SALESMENDRESS+SALES.SUM;
END;
ENDCOMP;
COMPUTE AFTER;
LINE @1 " ";
LINE @1 "NUMBER OF BOOT SALES :" @34 H_CNTBOOTS ;
LINE @1 "VALUE OF BOOT SALES :" @34 H_SALESBOOTSCASH DOLLAR18.2;
LINE @1 "NUMBER OF MEN'S CASUAL SALES :" @34 H_CNTMENCASUAL;
LINE @1 "VALUE OF MEN'S CASUAL SALES :" @34 H_SALESMENCASUAL DOLLAR18.2;;
LINE @1 "NUMBER OF MEN'S DRESS SALES :" @34 H_CNTMENDRESS;
LINE @1 "VALUE OF MEN'S DRESS SALES :" @ 34 H_SALESMENDRESS DOLLAR18.2;;
LINE @1 " ";
ENDCOMP;
RUN;
ODS PDF CLOSE;
ODS LISTING;
ODS HTML;
I want a table similar to the one generated by the below syntax (SYNTAX 2), but was unsure if there was a way to produce the same output using a single PROC REPORT. My concern is merely processing time to read the dataset when each PROC REPORT executes. I guess this could be overcome by reading the data into memory using the SASFILE STATEMENT.
SYNTAX 2
PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
COLUMNS SUBSIDIARY SALES;
DEFINE SUBSIDIARY / GROUP;
DEFINE SALES / SUM;
RUN;
Any opinions\insight would be greatly appreciated.
Thanks
Scott Mitchell
... View more
05-30-2018
01:27 AM
Thanks again @s_lassen.
I appreciate the rigor and additional consideration you have given my problem.
... View more
05-28-2018
09:43 PM
You are right Reeza and that is 100% why it was used in this context.
We have no control over the file structure and it tends to change at a whim. I think Tom's solution still allows me to account for the fluidity.
... View more
05-28-2018
08:34 PM
Hi Tom,
Thank you for your reply.
As I said there are current routines (macros) in place that are employed to read in CSV's. The first step of these routines is a PROC IMPORT to read in the first row (because all data thus far had the first row containing column headers). I have no control of these macros, however given the elegance of your solution I may be able to have those in charge of maintaining them change their minds.
Thank you for your help.
Regards,
Scott
... View more
05-28-2018
07:59 PM
Good Morning Everyone,
I have an existing process and an existing CSV file format by which I am constrained. I have no control over either and am required to undertake this task within those constraints and can not manually manipulate the file before consumption. I have researched the matter, however have been unable to locate a suitable solution. I also do not have access to Powershell or Python which seems to handle this task well.
So, now that the caveat is out of the way:
I have a text file which is separated by commas. This file arrives from a system on a monthly basis and the structure aligns with the agreements put in place some time ago (once again I have no ability to alter this agreement). The file arrives with a file header spanning 5 rows and a column header on the 6th. The current process is to pick up CSV files and import the column headers using PROC IMPORT. The data contained within the column header is then compared against the records within a control table. If the column names appear in both the CSV and the control table then the CSV is read in using the INFILE statement method (otherwise an email is sent requesting the new columns be mapped in the control table). Obviously the problem using the PROC IMPORT method is that it expects the column names to appear in the first row, which they don't in this instance. So to get past this constraint I decided to read the CSV in using the INFILE statement with a FIRSTOBS = 4 option and output the results to a temporary CSV file using the FILE statement, then consume the temporary Dataset using PROC IMPORT. The following code is what I have written:
FILENAME IN "&FILEPATH.\&FILENAME.";
FILENAME OUT "&FILEPATH.\TEMP_&FILENAME.";
DATA _TEST_;
INFILE IN FIRSTOBS = 6 LRECL = 32767;
FILE OUT LRECL = 32767;
INPUT;
PUT _INFILE_;
RUN;
The syntax appears sounds and I have seen similar suggestions posted by @Ksharp to deal with similar issues, however I end up with a blank temporary file and the following notes in the log.
NOTE: 0 records were written to the file OUT. NOTE: 0 records were read from the infile IN.
Common solutions I have found in regards to PROC IMPORT reference the DATAROW and NAMEROW options, however these do not function for text files.,
Environment details - SAS 9.4M4, Windows Server 2012.
Any suggestions on how to resolve this matter would be greatly appreciated.
Regards,
Scott
... View more
05-27-2018
11:54 PM
Thank you both for your help.
This worked perfectly.
... View more
05-25-2018
03:56 AM
Good Evening All,
I have a hierarchy which has holes in it as per the HAVE dataset code below. I am looking to first maintain the right most populated value whilst filling all blanks to the left (the code attached with the Array does this nicely). Then I need to find the right most populated record and hold it's value until ORG_LVL_10. I am struggling with the finding a solution that performs the latter task. Any help would be greatly appreciated.
Thank you for all your help.
Regards,
Scott
data have; length org_lvl_1 org_lvl_2 org_lvl_3 org_lvl_4 org_lvl_5 org_lvl_6 org_lvl_7 org_lvl_8 org_lvl_9 org_lvl_10 $20.; infile datalines dlm= ',' dsd missover; input org_lvl_1 org_lvl_2 org_lvl_3 org_lvl_4 org_lvl_5 org_lvl_6 org_lvl_7 org_lvl_8 org_lvl_9 org_lvl_10; datalines; LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8,LEVEL9,LEVEL10 LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5, LEVEL1,LEVEL2,LEVEL3,LEVEL4,,LEVEL6,LEVEL7,LEVEL8,LEVEL9,LEVEL10 LEVEL1,LEVEL2,LEVEL3,LEVEL4,,LEVEL6,LEVEL7,,,LEVEL10 ; run; data want; length org_lvl_1 org_lvl_2 org_lvl_3 org_lvl_4 org_lvl_5 org_lvl_6 org_lvl_7 org_lvl_8 org_lvl_9 org_lvl_10 $20.; infile datalines dlm= ',' dsd missover; input org_lvl_1 org_lvl_2 org_lvl_3 org_lvl_4 org_lvl_5 org_lvl_6 org_lvl_7 org_lvl_8 org_lvl_9 org_lvl_10; datalines; LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8,LEVEL9,LEVEL10 LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL5,LEVEL5,LEVEL5,LEVEL5,LEVEL5, LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL6,LEVEL6,LEVEL7,LEVEL8,LEVEL9,LEVEL10 LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL6,LEVEL6,LEVEL7,LEVEL10,LEVEL10,LEVEL10 ; run; DATA test;
SET have;
ARRAY _ORG $ ORG_LVL_1 - ORG_LVL_10;
J=1;
DO I=1 TO DIM(_ORG);
PUT I = J = _ORG(I) = _ORG(J) =;
IF TRIM(LEFT(_ORG(I))) NE "" THEN DO;
_ORG(J) = COALESCEC(TRIM(LEFT(_ORG(J))),TRIM(LEFT(_ORG(I))));
J+1;
END;
END;
RUN;
... View more
05-02-2018
06:10 AM
@JeffMeyers that is a truly outstanding solution. I can't wait to try it.
Thanks also to @Ksharp and @RW9 for their valuable input.
... View more
05-02-2018
06:06 AM
@RW9, thank you very much for your insight.
I spent so much time looking for an option for SGPLOT, that I completely neglected to investigate an option for ODS.
Thank you again
... View more
05-01-2018
06:00 AM
Thanks @RW9. I have spent a fair chunk of the day going through that blog (and others). It is a great resource.
I was also considering the concept of having 2 pie graphs side by side, but I doubt the stakeholders will buy into it. They are pretty committed to the existing look and feel, but may have to make some concessions.
Thank you again.
... View more
05-01-2018
05:44 AM
Good Evening All,
I have generated a bar chart similar to the one pictured below using SGPLOT (9.4m4). I have 2 questions regarding the outcomes:
1. Is there a way to remove or change the color of the border around the graph?
2. Can the Axis color be changed?
I have been able to customise the rest of the graph successfully, but this has me stumped.
Thank you for your help.
... View more
05-01-2018
05:00 AM
Good Evening Fellow SAS Boffins,
I am in the process of replacing a number of manually updated graphs in a Word document with SAS (9.4m4) alternatives. I have hit a dead end when it comes to replicating a Pie of a Pie Chart similar to the image below.
I am not married to the concept of using SAS/GRAPH and ODS if there is an AMO or VA alternative I would be keen to explore the idea.
Any help would be greatly appreciated.
... View more
08-03-2017
05:47 AM
Good Afternoon Community Members,
I am seeking opinions on the use of the Data Builder for data preparation.
There seems to be 2 schools of thought on the matter, based on developers I have discussed this with recently. Below are their positions as well as the pros and cons as they see it.
Data preparation should always be undertaken prior to data being loaded into the LASR server.
Pros
Trimming datasets of extraneous columns and data means that the table is taking up less memory on the LASR server.
Fewer processes undertaken on the LASR server means better performance for all end users and allows us to "get more done on the same budget".
Cons
VA is a self-serve, business focused reporting and analytic tool which needs to remain responsive to the businesses needs. Having to request I.T add a variable to a table(not to mention the lead time, testing and documentation required) output to the autoloader detracts from the businesses ability to remain "agile" and "responsive". I need to make the change now not in a fortnight.
Resources with the appropriate skillset need to be available to perform transformations, subsets and aggregations.
Data preparation should be undertaken within the data preparation tool unless entirely necessary.
Pros
Making changes to the existing dataset is simple using the intuitive interface. If I need to add a variable to my query I can do so in a short period of time, make the relevant change to my report and the business can consume the data within the same day (obviously an over simplification, but you get the point).
During the VA training course, the SAS trainer encouraged us to make use of the Data Query to perform our data preparation, that is what it is there for.
Memory is relatively cheap compared to processing power. Although we are using a finite amount of memory, the benefits of being able to return the end users report in a timely manner outweighs the cost associated with purchasing additional resources to meet existing needs.
Cons
The business users don't have the technical expertise to work with a (perceived) I.T. tool. They will fail to document the changes, test their outcomes or build sufficient rigour into their processes.
The resulting queries cannot be scheduled to occur in line with when the autoload process of the underlying table takes place. I can schedule for the query to be execute every day at Xam/pm or multiple times per day, but the user could access the report outside of these windows and make decisions based on old data. Read More...
Using the data query creates a second table with the LASR server with some duplicated information. This will cause us to run out of memory.
... View more