About ncsthbell

ncsthbell · ‎03-16-2016

I understand that but I am also not using a date prompt. I have searched and can not find anything that tells me the 'limitations' for the 'minimum' & 'maximum' values on a numeric prompt being created. In testing, in set the max & min amounts to a 10 digit values, it works. However, once I set the maximum value to an 11 digit value, I get an error (snapshot below). My users need to be able to enter a values up to a 11 digit number such as '99290058895'. I can't find anything that tells me there is a limit on the min & max values on a numeric prompt. I have several other prompts I need to build the same way so I am basically hung up on this right now.

ncsthbell · ‎03-15-2016

I created a shared numeric prompt (From_Amt) in SAS Management Console since I will have 10 reports using this prompt. When I built the prompt, it had settings for 'minimum' & 'maximum' values allowed. I assume this is the value, not the length so I did not set it. Note, I also built prompt (To_Amt) the same way. I built the first stored process pulling in this shared prompt. When I test running the STP, at the prompt for 'From_Amt' I entered a 10 digit numeric value (0123456789) 10 digits. A red warning icon appears and displays 'invalid value for this prompt'. If I change the From_Amt' to value (012345678) 9 digits, it accepts this. If I have no min & max amounts defined, why does it accept a 9 digit From_Amt but errors on 10 digit From_Amt. I am very confused on this! Any suggestions would be appreciated! Update: In testing, in set the max & min amounts to a 10 digit values, it works. However, once I set the maximum value to an 11 digit value, I get an error (snapshot below). My users need to be able to enter a values up to a 11 digit number such as '99290058895'. I can't find anything that tells me there is a limit on the min & max values on a numeric prompt. I have several other prompts I need to build the same way so I am basically hung up on this right now.

ncsthbell · ‎03-09-2016

I have spent over a day trying to get this to work, getting very frustrated! The report I am building has 15 prompts (user wants) and I can't get the results correct on the first one. The code runs but my 'where clause' results is not correct. I need to build the 'where clause' based on the prompts they enter. They can leave any of them blank. I appreciate any suggestions. Here is my code & the result of the 'where statement': %LET LINE1_FROM_AMT = 16000; %LET LINE1_TO_AMT = 17000; %macro BuildWhereClause; %global where_clause; %let where_clause = &where_clause; %if %eval(%length(&line1_From_Amt) > 0) and %eval(length(&Line1_To_Amt) > 0) %then %do; %let where_clause = &where_clause and (&Line1_From_Amt >= SALES_LINE1 and &Line1_From_Amt <= SALES_LINE1); %end; %mend; %BuildWhereClause; Result: 148 %put WHERE STMT IS: &where_clause.; WHERE STMT IS: and (16000 >= SALES_LINE1 and 16000 <= SALES_LINE1)

ncsthbell · ‎03-08-2016

I am building a program which gives the user about 10 different prompts in which they can populate. I am trying to build a 'where' statement based on testing the length of the prompt to see a value was entered. I have just started with testing the first prompt which is a 'from' & 'to' amount. I can't get the first one correct! I am including my code and the error I am getting. Any guidance would be greatlyh appreciated!! /* Build 'where' clause based on filters entered */ %macro BuildWhereClause; %global where_clause; %let where_clause = 1=1; /*- TYPE1_amounts - */ %if %length((&TYPE1_From_Amt > 0) and (&TYPE1_To_Amt > 0)) %then %do %let where_clause = &where_clause and (&TYPE1_From_Amt >= TYPE1_LINE1 and & TYPE1_From_Amt <= TYPE1_LINE1); %end; %mend; %BuildWhereClause; ERROR: Expected %TO not found in %DO statement. A dummy macro will be compiled. 125 %end; 126 %mend; 127 %BuildWhereClause; _ 180 WARNING: Apparent invocation of macro BUILDWHERECLAUSE not resolved. ERROR 180-322: Statement is not valid or it is used out of proper order.

ncsthbell · ‎03-04-2016

Thanks Ksharp! I got it to work after trying many different ways. This is how I wound up getting the leading zeros: Define ID / display 'Entity ID' style(column)=[font_size=8pt width=.7in tagattr='Format:@'] style(header)=[font_size=8pt font_face='Times' font_weight=bold just=LEFT]; I think my problem was I was trying to keep the '{' character inside the [font....]. When I removed the '{', it worked!

ncsthbell · ‎03-03-2016

My copy/paste did not get all of the lines intended for under the 'CHANGED' for the code. Here are all the lines: Define ID / display 'Entity ID' LEFT style={tagattr='Format:@'}

ncsthbell · ‎03-03-2016

I have researched the communities board on this and found learned that character data is changed to numeric data when it is output to excel via ODS. I have an 'ID' column which is a character format and can contain leading zeros. I am using PROC REPORT. I can't find any examples when using this. I have tried the following but it does not work. Maybe I am placing the tagattr in the wrong place? Any help would be appreciated!! ORIGINAL proc report data=work.EOM_ACCCOUNTS; columns ID; Define ID / display 'Entity ID' LEFT style(column)=[font_size=8pt width=.5in] style(header)=[font_size=8pt font_face='Times' font_weight=bold just=LEFT]; CHANGED: proc report data=work.EOM_ACCCOUNTS; columns ID; Define ID / display 'Entity ID' LEFT style={tagattr='Format:@'}

ncsthbell · ‎02-25-2016

Thanks so much for your assistance!

ncsthbell · ‎02-23-2016

I have built a stored process that in which I built prompts in Management Console for 'Name', 'Address', 'City' and 'State' columns. If the user enters a value in either the prompt for 'Name' or 'Address', they want the 'City' & 'State' prompt fields to be required to be populated. From what I have read, the cascading prompt associates a list of values to choose from for a given prompt. I do not see any options in SMC that I could set to make the 'Name' & 'Address' prompts require a value to be entered into the 'City' & 'State' prompts if the user enters values in either or both the 'Name' & 'Address' prompts.. I would appreciate any direction on how to accomplish this task. Thanks is advance!!

ncsthbell · ‎02-16-2016

Thanks so much RW9.... your example was exactly what I needed it to do!

ncsthbell · ‎02-16-2016

I had the following in a data step first and got errors on this as well. I changed it to use a macro to see if I could get it to work. I agree that data step is best way... just couldn't get it to work. As you can tell, I do not have a lot of experience with SAS code. Here is what I had in Data step: data _null_; /* build new prompt columns to use wildcard */ if &NAICS_CD = '%' then call symput('NEW_NAICS_CD',"'"||"&NAICS_CD"||"'"); else call symput('NEW_NAICS_CD',"'"||"&NAICS_CD"||"%'"); RUN; The error says: 59 data _null_; 60 /* build new prompt columns to use wildcard */ 61 if &NAICS_CD = '%' then call symput('NEW_NAICS_CD',"'"||"&NAICS_CD"||"'"); ____ 180 ERROR: Undeclared array referenced: symput. 61 if &NAICS_CD = '%' then call symput('NEW_NAICS_CD',"'"||"&NAICS_CD"||"'"); _ 22 ERROR 180-322: Statement is not valid or it is used out of proper order. ERROR 22-322: Syntax error, expecting one of the following: +, =.

ncsthbell · ‎02-16-2016

I have the following code in my program and it is returning the value of '%%' (the else condition) when it should be returning the value of '%'. What am I doing wrong? %LET TYPE_CD = %; %macro TYPECD; %if &TYPE_CD = '%' %then call symput("NEW_TYPE_CD","'"||"&TYPE_CD"||"'"); /*leave variable as is*/ %else call symput("NEW_TYPE_CD","'"||"&TYPE_CD"||"%'"); /wrap with % %mend TYPECD; NEW_TYPE_CD is returning value of '%%', should be '%' TYPE_CD is defined in SMC as a prompt with default value of '%' If user leaves default, I want to select all type codes, so format is '%'. If user enters '722', I want to format as '722%' to select all type_cds that begin with '722'. I am trying to assign these values in the macro.

ncsthbell · ‎02-15-2016

I have defined one prompt named ‘whichcolumn’. Based on the length of the value the user enters into the prompt I need to determine which column to use in my ‘where’ clause. Is this possible to build as part of a where clause? If not, are there any other suggestions as to how I can accomplish this? My table columns: Column2 Column2Desc Column3 Column3Desc Column4 Column4Desc Example: If the user enters ‘72’, the length is 2 so I want to use Column2 in my where clause. If the user enters ‘721’, the length is 3 so I want to use Column3 in my where clause. If the user enters ‘7211’, the length is 4 so I want to use Column4 in my where clause. Thanks for any assistance!

ncsthbell · ‎02-11-2016

I am still not getting the results that I want in the step to check for observations in the dataset. The data set is created with 3 rows, however, after running the data code to check the obervastions in the dataset, it returns the value of '0' which indicates no rows. I have never used this before and don't know what I have wrong in the data step. All I need to know is if there is data or not and the '0' means no data. Is this the best way to check? The Work.Financial_CORP datasest shows it has 3 rows. I used a put statement to display the value of CORPobs and it is showing value of 0. Here is the displayed results from log: NOTE: The query requires remerging summary statistics back with the original data. NOTE: Table WORK.FINANCIAL_CORP created, with 3 rows and 35 columns. 180 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 181 182 /*Check if data set has data and set variable as a flag to*/ 183 /* trigger the CORP report(tab) to be created. */ 184 data _null_; 185 call symput('CorpObs','0'); /* set to 0 for No */ 186 set Work.Financial_CORP(obs=1); 187 call symput('CorpObs','1'); /* if got here there are obs so set to 1 for Yes */ 188 run; NOTE: There were 1 observations read from the data set WORK.FINANCIAL_CORP. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 189 %put &CorpObs; 0

ncsthbell · ‎02-11-2016

I am SO sorry for not seeing this! Just kinda of overwhelmed right now with so many things on my plate. I'm sure you know how that feels. Thanks so much for your assistance.

Online Status	Offline
Date Last Visited	‎04-11-2016 09:58 AM

Re: Why am I getting error when using function 'length' to test a vari...

Why am I getting error when using function 'length' to test a variable...

Re: Need guidance for best way to capture user entered prompts and cre...

Re: Need guidance for best way to capture user entered prompts and cre...

Re: Need guidance for best way to capture user entered prompts and cre...

Need guidance for best way to capture user entered prompts and create ...

Re: When stored process using prompts runs on SAS portal can a message...

When stored process using prompts runs on SAS portal can a message be ...

Re: Can not get macro variable to resolve to correct value.

Can not get macro variable to resolve to correct value.

Re: General questions about using forum.

Using EG 4.2 - how to extract 1st 3 digits from numeric column?

Prompt not coming up when running query builder

Re: Trying to format column Proc Report using ODS TAGSETS.EXCELXP to c...

Re: How can I change the size/length of a numeric shared prompt?

Are there limits on the nbr of digits that can be used for MIN & MAX v...

Re: Trying to build 'where' clause based on user inputs, getting error...

Trying to build 'where' clause based on user inputs, getting error and...

Re: Trying to format column Proc Report using ODS TAGSETS.EXCELXP to c...

Re: Trying to format column Proc Report using ODS TAGSETS.EXCELXP to c...

Trying to format column Proc Report using ODS TAGSETS.EXCELXP to chara...

Re: Using Proc SQL, how to determine which column to use in where clau...

How to make one prompt required IF data is entered into a different pr...

Re: How to reformat a value using macro if, then, else. Can't get mine...

Re: How to reformat a value using macro if, then, else. Can't get mine...

How to reformat a value using macro if, then, else. Can't get mine to ...

Using Proc SQL, how to determine which column to use in where clause b...

Re: Problems checking dataset if observations exist.

Re: Problems checking dataset if observations exist.