SAS Procedures topics

Accept Self-signed certificate with PROC HTTP

valengvz — Thu, 22 Jul 2021 14:49:37 GMT

Hi there, I'm trying to translate this code into SAS code with PROC HTTP

curl --insecure -k  https://server/oauth/token -H "Accept: application/json" -H "Content-Type: application/x-www-form-urlencoded" -d "grant_type=password&username=user&password=xxxx" -u client:clientsecret"

So far, I have this SAS code

proc http url="&server_host/&server_endpoint_token"
    in=jsonin
    webusername="client"
    webpassword="clientsecret"
    CT="Content-Type=application/x-www-form-urlencoded"
    out=jsonout
    headerout=headout;
	debug level=3;
    headers "Accept"="application/json" ;
run;

The problem is that I have to accept the self-signed certificate but I don't know how to implement that in the PROC HTTP procedure.

Does someone know how to do that?

proc lifetest Brookmeyer-Crowley confidence interval for median: conftype=?

fengyuwuzu — Wed, 21 Jul 2021 22:57:08 GMT

since SAS 9.2, the default CONFTYPE=LOGLOG in proc lifetest.

if I want calculate BrookMeyer-Crowley confidence interval for the medial survival, must I set CONFTYPE = LINEAR?

How do I know the confidence interval I got is Brookmeyer-Crowley CI?

t grouping is showing in bar not letter

atikurmamun — Sun, 18 Jul 2021 20:41:03 GMT

i want to know when i see the sas 1st time i see the t grouping comes out with letter and means but nowadays i see it is coming with means and bar diagram though the value is ok for me, it is hard to present in front of someone cause they sometimes couldn't understand the bar but they can understand the lettering.is there anyone who can help me withit is new styleit is old one where grouping comes with lettering this?
i have attached 2 pictures for understanding my question. Number 1 is new and number 2 is the old one. please give me a solution.

OPTION Groups Enterprise Guide versus Windowing Environment

FK1 — Wed, 14 Jul 2021 13:08:14 GMT

Hi Everybody,

can someone explain to me, why I get a different number of OPTION Groups (and presumably also a different number of options! ) when runninng PROC OPTIONS in SAS Enterprise Guide like this:

proc options listgroups long; 
run;

which results in the following Log output:

GROUP=ADABAS ADABAS
GROUP=ANIMATION Animation
GROUP=CAS CAS Options
GROUP=CODEGEN Code generation
GROUP=COMMUNICATIONS Networking and encryption
GROUP=DATACOM Datacom
GROUP=DATAQUALITY Data Quality
GROUP=DB2 DB2
GROUP=EMAIL E-mail
GROUP=ENVDISPLAY Display
GROUP=ENVFILES Files
GROUP=ERRORHANDLING Error handling
GROUP=EXECMODES Initialization and operation
GROUP=EXTFILES External files
GROUP=GRAPHICS Driver settings
GROUP=HELP Help
GROUP=IDMS IDMS
GROUP=IMS IMS
GROUP=INPUTCONTROL Data Processing
GROUP=INSTALL Installation
GROUP=ISPF ISPF
GROUP=LANGUAGECONTROL Language control
GROUP=LISTCONTROL Procedure output
GROUP=LOGCONTROL SAS log
GROUP=LOG_LISTCONTROL SAS log and procedure output
GROUP=MACRO SAS macro
GROUP=MEMORY Memory
GROUP=META Metadata
GROUP=ODSPRINT ODS Printing
GROUP=PDF PDF
GROUP=PERFORMANCE Performance
GROUP=REXX REXX
GROUP=SASFILES SAS Files
GROUP=SECURITY Security
GROUP=SMF SMF
GROUP=SORT Procedure options
GROUP=SQL SQL
GROUP=SVG SVG
GROUP=TK TK

versus using the SAS Windowing Environment by opening the SAS Options Environment Window via command line:

and then counting the OPTION GROUPS, that are shown:

In the first case, I counted 39 Option Groups, while in the later case, I only got 31 (I did not count the "Top Folders" like "Communications", "Environment Control", etc. since they do not have any options but only seem to be an aggregation of several Option Groups).

In essence, I am observing a deviance in the sense of not being displayed of the following OPTIONS GROUPS in Windowing Environment:

ADABAS
Datacom
DB2
IDMS
IMS
ISPF
REXX
SMF

How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 09:16:54 GMT

Hello everyone,

I am seeking help to write a macro using proc nlmixed with multiple terms.

I want to define the model in proc nlmixed with multiple dummies (let say D1, D2, D3, D4, D5) and the interaction terms between variable X with each of those dummy variables.

The code can be written normally as:

proc nlmixed data=have;

eta = int + a1*D1 + a2* D2 + a3*D3 + a4*D4 + a5*D5 + b1*D1*X + b2*D2*X + b3*D3*X + b4*D4*X + b5*D5*X;

......

run;

My actual model is much longer than that since I have up to 9 dummies and 2 variables X. In total, I will need to specify around 30 terms in my model. Is there way to make my model specification shorter which allows me to modify the model easily?

Thank you so much in advance.

Windy.

Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Mon, 12 Jul 2021 20:12:00 GMT

I have two tables, first and second, that contains data from the American Community Survey (ACS). The first table has missing values (always across 4 variables), whereas the second table contains values I wish to fill in for the missing values in the first table (through matching/joining by PAT_ID).

/* ACS variables at census block level */
data first;
input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@;
datalines;
1 45810 93.82 13.07 9.32
2 35264 54.94 27.81 4.76
3 . . . . 
4 . . . . 
5 42346 18.62 1.87 1.29
6 42346 41.11 4.76 10.29
7 250001 41.11 3.02 1.29
8 115290 93.82 27.81 1.87
9 215493 21.65 10.29 3.60
10 . . . . 
11 215493 15.61 27.81 3.02
12 77521 18.62 7.17 9.32
13 215493 54.94 5.95 27.81
14 . . . . 
15 115290 34.35 1.29 9.32
16 30872 34.35 10.29 7.17
17 105039 15.61 4.76 13.07
18 . . . .  
19 38964 9.55 4.76 2.45
20 38964 54.94 10.29 10.29
;

/* variables at the zipcode level (INCOME_ZIPCODE EDU_ZIPCODE UNEMPLOY_ZIPCODE HOMEOWNER_ZIPCODE)
 but named the same as above to help with merging*/ 
data second; 
input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@; 
datalines; 
1 50127 8.33 35.00 82.71 
2 43578 29.01 44.06 58.68 
3 36251 14.93 50.14 62.01 
4 136686 14.93 29.44 67.86 
5 136686 22.49 33.52 87.99 
6 43578 22.49 50.14 87.99 
7 32374 10.86 33.52 64.28 
8 39755 7.51 56.55 58.68 
9 34548 12.48 56.55 80.46 
10 34548 14.93 38.84 58.68 
11 50127 72.73 31.91 82.71 
12 50127 9.22 50.14 87.99 
13 37981 14.93 44.06 75.23 
14 61270 8.33 41.37 87.99 
15 57101 8.33 50.14 80.46 
16 36251 4.79 38.84 62.01 
17 136686 8.33 48.14 80.46 
18 37981 72.73 31.91 67.86 
19 50127 9.22 33.52 62.01 
20 136686 19.81 41.37 62.01 
;

/* values filled in for missing are from zip-code level instead of census block level */

data want;

input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@; datalines;

1 45810 93.82 13.07 9.32
2 35264 54.94 27.81 4.76
3 36251 14.93 50.14 62.01
4 136686 14.93 29.44 67.86
5 42346 18.62 1.87 1.29
6 42346 41.11 4.76 10.29
7 250001 41.11 3.02 1.29
8 115290 93.82 27.81 1.87
9 215493 21.65 10.29 3.60
10 34548 14.93 38.84 58.68
11 215493 15.61 27.81 3.02
12 77521 18.62 7.17 9.32
13 215493 54.94 5.95 27.81
14 61270 8.33 41.37 87.99
15 115290 34.35 1.29 9.32
16 30872 34.35 10.29 7.17
17 105039 15.61 4.76 13.07
18 37981 72.73 31.91 67.86
19 38964 9.55 4.76 2.45
20 38964 54.94 10.29 10.29

;

How to print only first page ODS PDF

Easybeat — Mon, 12 Jul 2021 09:48:39 GMT

Hello!
I’ve got quite big proc tabulate report on 90 pages. I need to print it out on PDF, but only the first page. How do I do it?

Renaming variable based on a correlation file and Subquerry error

Blandine — Fri, 09 Jul 2021 15:20:42 GMT

Hello, I am having a file with data fragmentation. The variable "Structure_name" (in the file WA.WA_datanewname) has 2 ID for the same bridge and I'm trying to make it 1 ID using a correlation file (fh_wa02) that was given to me. The correlation file has old_structure_number_008 and New_structure_number_008, so basically replace I want to replace the old_structure_number_008 with the New_structure_number_008 without lossing any data. But I am having 2 problems:

1- The subquery error below. I solve it by deleting that structure number which is probably not an efficient solution

ERROR: Subquery evaluated to more than one row.
NOTE: Correlation values are: Structure_Number='WN-005051656925

2. It's seemed like I am losing some bridge ID. I have 4 bridges that I checked if the code correctly replace the old_structure_number_008 with the New_structure_number_008 but the code simply deleted the old_structure_number_008 . I don't know what to do

Here is my code

proc sql;
create table WA.WA_NBI_data_corrected as
select
case when exists (select * from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
then (select NEW_STRUCTURE_NUMBER_008 from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
else Structure_Number
end as Structure_Number
, Year_Built
, ADT
, STRUCTURE_KIND_043A
, STRUCTURE_KIND_043B
, Bridge_Age
, DECK_COND_058
, SUPERSTRUCTURE_COND_059
, SUBSTRUCTURE_COND_060
, Deck_Structure_Type
, Inspection_Year
from WA.WA_datanewname;
quit;

PROC IMPORT Excel hyperlink to a SAS data set

Dick_Pickett — Wed, 07 Jul 2021 20:10:48 GMT

Is this possible?

Example: An Excel cell contains: =HYPERLINK("[mailto:SNFVBP@rti.org]", "SNFVBP@rti.org")

PROC IMPORT does not import the cell contents.

PROC IMPORT datafile = "report" out = &tabnamecon
dbms = XLSX REPLACE ;

sheet = "&tabname" ;
getnames = NO ;
%IF &columnheadline = NA %THEN %DO ;
datarow = 1
%END ;
%IF &columnheadline ^= NA %THEN %DO ;
datarow=&columnheadline
%END ;
;
RUN ;

Proc Report

Eugenio211 — Wed, 07 Jul 2021 16:32:40 GMT

Hello, I wanted to add sub totals and Total to my proc report steps. I am showing this in an excel format. below is the code that I have

ods excel file="directory path\Test_List;

proc report data=Test_List;
columns dm location campaign_month, (customer_count converted Conversion_Rate);
define dm/group;
define location/group;
define campaign_month/across order=data;
define customer_count/sum 'Preapprovals';
define converted/sum 'Conversion';

run;

ods excel close;

Current Output:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Desired Ouput:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	SubTotal
	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	SubTotal
	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	SubTotal
	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Thanks a lot.

how do I use if/and/delete statement to delete sex-dependent values

ayosas — Tue, 06 Jul 2021 23:15:31 GMT

set ile.nhanes2016 (keep= SMQ040 IMQ060 IMQ070 IMQ100 RIAGENDR 
RIDAGEYR DMDCITZN DMDMARTL BMXBMI DMDEDUC2 INDHHIN2
MCQ220);
if IMQ070=. and IMQ060=. then delete;
run;

proc freq data=ile.nhanes2016;
tables IMQ060 IMQ070 / nocum missing;
run;

IMQ070 value is for males and IMQ060 is for females. Subjects have a value for one and not the other. I want to delete missing values. After running the code missing values remain for both males and females, is there something wrong with my code?

What would be the numeric limit for percentage in SAS?

StickyRoll — Sun, 04 Jul 2021 03:22:28 GMT

I was testing SAS numeric limit for percentage calculation.

For example,

40% is 0.4 in SAS Dataset
100% is 1 in SAS Dataset
33.333333% is 0.3333333 in SAS Dataset

Another Database team is going to populate percentage data for me. Now the question is, I tried testing in BASE SAS and I found out SAS can support up to 8 decimal places as followed.

0.12345678

There might be cases where by 100 divides by 3 will get 33.3333333.

For this, it would be Num8. with format 10.8. Is there a better way to define a SAS column for percentage instead of defining format 10.8 with Num8. especially for cases like 33.333333?

How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 09:55:42 GMT

I have an excel workbook which contains random sheets every month.

As such, I want to have a script to load this excel workbook without hardcode any of the worksheet name inside the script. Is there a way in SAS where I can get the list of all sheets in the excel?

This way, I can assign the list to macro and easily store in SAS.

Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 19:22:50 GMT

HI,

Just wondering if someone can help. I'm trying to import weighted data from SPSS into SAS. The file imports, however, the weighted data does not import properly and the original sample size remains. I've tried to find this info in user manuals, but can't seem to find anything. Any ideas what I'm doing wrong?

thanks

Creating macro vars using PROC SQL select into

Walternate — Fri, 25 Jun 2021 17:24:00 GMT

Hi all,

I have a list of tens of thousands of valid values for a set of variables and am trying to set up a lookup to determine which records have invalid values for these vars. I am trying to use PROC SQL select into to create a macro list of valid values for the variable:

proc sql noprint;

select(value) into :list_of_values separated by ','

from myfile;

quit;

With this in place, I can just set up a lookup for whether the value of a given variable matches one of the values in &list_of_values.

The problem is that I have too many values listed so SAS is indicating that the &list_of_values macro variable cannot be built because it would be too long. Is there a workaround for this? The only thing I can think to do is break up the variable list into multiple macro lists, but that is a bit of a pain.

SAS Access to Snowflake

garyhaas — Tue, 22 Jun 2021 22:58:42 GMT

Want to use Bulkload to an existing Snowflake table that has been created using a Create table statement. But libname bulk load complains that table already exists. Is there a way to use bulkload using a connect statement with execute commands?

Diference Date (MONTH) by ID

Andres_Fuentes1 — Tue, 22 Jun 2021 17:19:34 GMT

Hello good morning, I could not find the way to do the following, I have an ID and a date of a certain event. I must add the difference in months of an event with the following one by ID.

Example in excel in the photograph.

someone who can help me, Greetings.

Defining a variable within a date interval

Bright — Sat, 19 Jun 2021 16:46:51 GMT

Hello,

I have a dataset looks like this:

Location	Date	Number of warnings (N)	Number of correct warnings (T)
A	06/19/2020	3	2
A	06/29/2020	5	3
A	06/30/2020	1	1
A	07/1/2020	4	3
A	07/05/2020	7	1
A	07/15/2020	2	0
A	07/19/2020	3	1
A	….	…	…
B	05/12/2020	4	4
B	05/24/2020	1	0
B	05/27/2020	3	2
B	…	…	….

For each specific “Date” I want to calculate R=Sum(T)/Sum(N) during the last month. For example, if we consider Location “A” and Date “07/19/2020”, I want to have R=(0+1+3+1+3+2)/(2+7+4+1+5+3).

Any help is appreciated!

Thanks

Create a dataset using proc tabulate

sam_sas2 — Wed, 16 Jun 2021 13:53:43 GMT

Hi all,
So I am currently running the below proc tabulate query,

proc tabulate data=sashelp.cars (where =(make in("Acura","Audi"))) out=cars;
class make;
var msrp;
table (msrp * (N MEAN MEDIAN MIN MAX)),
	  ALL make;
run;

And the output dataset from OUT statement looks like this(first 5 rows):

Obs	Make	_TYPE_	_PAGE_	_TABLE_	MSRP_N	MSRP_Mean	MSRP_Median	MSRP_Min	MSRP_Max
1		0	1	1	26	.	.	.	.
2	Acura	1	1	1	7	.	.	.	.
3	Audi	1	1	1	19	.	.	.	.
4		0	1	1	.	43208.46	.	.	.
5	Acura	1	1	1	.	42938.57	.	.	.
6	Audi	1	1	1	.	43307.89	.	.	.

But, I'd like to create an ADS that looks like this,

Type	All	Acura	Audi
MSRP_N	26	7	19
MSRP_Mean	43208.46	42938.57	43307.89
MSRP_Median	40115	36945	40590
MSRP_Min	23820	23820	25940
MSRP_Max	89765	89765	84600

I have tried using proc transpose, I'm unable to figure out how to deal with the nulls and also label them with "Make"

proc transpose data=cars out=cars2; var msrp_:;

Any sort of help would be appreciated. Thanks in advance! 🙂

Count frequencies of dates in a column and then graph them by Half-month intervals

PharmlyDoc — Wed, 16 Jun 2021 12:50:48 GMT

I have a dataset called record with patients who activated an online account.

I have a column of dates as follows and I want to count the frequencies that each date occurs such that I can graph the number of activations (as a line over time) that occur every 2 weeks.

How should I go about doing this?

Activation_Date

2020/10/29:08:15:09 AM

2020/10/23:12:31:33 PM

2020/10/04:02:45:36 PM

2020/10/23:12:46:17 PM

2020/10/04:02:24:47 PM

2020/10/23:01:41:20 PM

2020/10/04:08:20:47 AM

2020/10/23:11:24:18 AM

2020/09/13:10:11:59 AM

2020/09/04:09:15:59 AM

2020/09/23:07:07:17 AM

2020/09/04:02:28:07 PM

2020/10/23:12:19:19 PM

So for example, in Excel, it would look like this:

How to increase your estimate and standard error output to 6 decimals under glimmix procedure

kellychan84 — Tue, 08 Jun 2021 21:07:21 GMT

proc glimmix data=data1; 
  class treatment block;
  model weight = treatment;
  random block;   
run;

By using the SAS OnDemand for Academics, the default decimal number of the output is 4, how to increase to 6. Thank you very much in advance!

Join datasets by group in sql

xyxu — Tue, 08 Jun 2021 16:41:55 GMT

How to implement the following in proc sql?

proc sort data = set1; by x, y; run;
proc sort data = set2; by x, y; run;

data set3;
merge set1 (in = a) set2 (in = b);
by x y;
if a and not b;
run;

I am trying to do this by group, and the group identifier is commonly available in set1 and set2.

How to combine numeric year and month into one column as a date format?

dbwindyxiaosi — Mon, 07 Jun 2021 14:53:15 GMT

Hi guys,

My data SUMDATE is like below: I want to combine year and month into a single date column as "monyyyy" (Mar2011 as example). year and month are all numeric.

year	month	cases
2011	1	34
2011	2	44
2011	3	56
2011	4	45
2011	5	33
2011	6	44
2011	7	55
2011	8	66
2011	9	32
2011	10	52
2011	11	54
2011	12	56
2012	1	58
2012	2	60
2012	3	62
2012	4	64
2012	5	66
2012	6	68
2012	7	70
2012	8	72
2012	9	74
2012	10	76
....more lines	....	.....

What I want is like this:

date1	cases
Jan2011	34
Feb2011	44
Mar2011	56
Apr2011	45
more lines.....	......

Any help would be appreciate!!!

Extract part of strings - before or after delimiter

alw12194 — Fri, 04 Jun 2021 20:01:55 GMT

Hi! Sorry if this has already been posted but cannot seem to find a previous post on this.

I have a variable in a large data set (~50,000 obs so too many to manually correct) that can take on 1 of 3 possible structures:

1. name (example: Hartsfield airport)

2. xxxx - name (example: 12345 - Hartsfield airport)

3. name - xxxx (example: Hartsfield airport - 12345)

Here is some code to create a sample in SAS.

data trial;
input have $;
datalines;
"12345 - hartsfield airport"
"hartsfield airport - 12345"
"hartsfield airport"
;

The strings are of many differing lengths and the numbers can be any combo and length so my sample may be oversimplified. I am wanting just the name out of the variable's string. There is no rhyme or reason for there being numbers and a dash before or after the name so I cannot subset the datasets based on another variable and use a simple strip(scan(have, 1, "-")) or strip(scan(have, -1, "-")) to extract just the name. I really wish it was that simple. Is there a way to modify the strip and scan functions or another function I should use to extract name?

I have thought about using something like the FIND, INDEX, or ANYDIGIT function to do a subset and then apply the strip/scan functions but because the numbers can take on any value and the strings have varying lengths, I don't think those will work. Any advice is appreciated. Thanks!

Proc univariate : Conf intervals for percentiles

kaet78ha — Tue, 01 Jun 2021 09:39:38 GMT

I'm trying to calculate the following stats :

25th percentile (95% CI)
Median (Weeks) (95% CI)
75th percentile (95% CI)

by using proc univariate

And I'm getting the results:

But I'm a little bit confused with output CI to the datset, I'm trying like:

ods select Quantiles;
proc univariate data=Sashelp.Cars ciquantnormal(alpha=.1 );
var MPG_City;
output out=a1 q1=q25 q3=q75 median=median min=min max=max lclm=lci uclm=dfd;
run;

But I'm getting an error :

ERROR 22-322: Syntax error, expecting one of the following: CSS, CV, GEOMEAN, GINI, KURTOSIS, MAD, MAX, MEAN, MEDIAN, MIN, MODE,
MSIGN, N, NMISS, NOBS, NORMAL, P1, P10, P5, P90, P95, P99, PCTLNAME, PCTLPRE, PROBM, PROBN, PROBS, PROBT, Q1, Q3, QN,
QRANGE, RANGE, SIGNRANK, SKEWNESS, SN, STD, STDMEAN, STD_GINI, STD_MAD, STD_QN, STD_QRANGE, STD_SN, SUM, SUMWGT, T,
USS, VAR.

I suppose I'm using the wrong way for output CI, can somebody help?

passing parameter using Proc http get method.

Akshat360 — Mon, 31 May 2021 13:41:12 GMT

I am attempting to kickstart a job on an API. I am able to get a valid response without sending the _since parameter in the below curl command.

curl -X GET 'https://api.bcda.cms.gov/api/v1/Patient/$export?&_since=2020-02-13T08:00:00.000-05:00' \
	-H "Accept: application/fhir+json" \
	-H "Prefer: respond-async" \
	-H "Authorization: Bearer {access_token}"

Here is my Proc http procedure, alos i have tried passing the _since parameter using IN but no success.

Link for API documentation:https://bcda.cms.gov/build.html#requesting-since-with-Patient

	proc http         		
			url="https://api.bcda.cms.gov/api/v1/Patient/$export"         
         	out=jobresp
        	method="GET"
         	headerout =jobhead;
            headers "Accept"="application/fhir+json"
        			"Prefer"="respond-async"
        			"Authorization"="Bearer &access_token. ";
		run;

proc mixed crashed when running these two options (influence and by statement)

juen1 — Sun, 30 May 2021 22:31:52 GMT

PROC MIXED procedure crashes when I use influence option and by statement at the same time. Below are my SAS code and error message in the log. Any idea? Thanks,

proc sort data=blood; by OrderName ObservationName; run;

proc mixed data=blood;
class subjectID Diet;
model value = Diet / influence(iter=5 estimates) ;
random SubjectID;
by OrderName ObservationName;
estimate 'trt effect' diet 1 -1;
quit;

Error message in log

NOTE: Convergence criteria met.
NOTE: The above message was for the following BY group:
OrderName=C-Peptide ObservationName=C-Peptide, Serum
ERROR: File WORK.UTRAW.UTILITY is damaged.
ERROR: Utility file I/O.
ERROR: File WORK.UTRAW.UTILITY is damaged.
NOTE: PROCEDURE MIXED used (Total process time):
real time 1.18 seconds
cpu time 0.39 seconds

Estimating parameters for each group separately (multiple group data)

UmarKhan — Fri, 28 May 2021 23:58:54 GMT

Hi,
I need to estimate parameters a and b as well as R-squared from data for each group (expno=1, expno=2, expno=3 etc.,) separately at once. Could someone help me to modify the below codes? Many thanks

FILENAME CSV "/folders/myfolders/umar2021.csv" TERMSTR=CRLF;

PROC IMPORT DATAFILE=CSV
		    OUT=umar
		    DBMS=CSV
		    REPLACE;
RUN;
title 'LWR for shells';
data exp;
set umar;
if expno=1 or expno=2 or expno=3;
run;

proc nlin data=exp method=marquardt;
parms B=3 A=0.01;

model w=A*tl**B;


output out=expp p=pw r=w_residual;
run;
proc summary data=expp;
     var w_residual w;
     output out=stats css(w)=sstot uss(w_residual)=ssres N=N;
run;
data expp;
     set stats;
     rsquared=1-(ssres/sstot);
     adjrsquared = 1-(1-rsquared)*(N-1) / (N- 3  -1);
run;
proc print data=expp;
run;

PROC TABULATE- Suppressing Columns

sam_sas2 — Thu, 27 May 2021 17:45:54 GMT

I am currently running this following code,

proc tabulate data=sashelp.cars;
class origin drivetrain type;
var msrp;
table (drivetrain * (N))
	  (msrp * (N MEAN MEDIAN MIN MAX)),
	  ALL origin type/ misstext='0';
run;

The current output looks something like this,

		All	Origin			Type
			Asia	Europe	USA	Hybrid	SUV	Sedan	Sports	Truck	Wagon
DriveTrain
All	N	92	34	36	22	0	38	28	5	12	9
Front	N	226	99	37	90	3	22	179	8	0	14
Rear	N	110	25	50	35	0	0	55	36	12	7
MSRP	N	428	158	123	147	3	60	262	49	24	30
	Mean	32774.86	24741.32	48349.8	28377.44	19920	34790.25	29773.62	53387.06	24941.38	28840.53
	Median	27635	23032.5	40590	25520	20140	32062.5	26432.5	40590	22180	25545
	Min	10280	10280	16999	10995	19110	17163	10280	18345	12800	11905
	Max	192465	89765	192465	81795	20510	76870	128420	192465	52975	60670

The issue I'm facing currently is, I do not want to display columns Sedan and Sports but at the same time I do not want my over all count for Origin or All to get affected. Is it possible to achieve this by any means?

Thanks in advance! 🙂

Character to Date Format

raivester — Wed, 26 May 2021 14:00:09 GMT

I have a date variable in character format, length 10, format and informat $10. Examples of the values are 1/5/2010, 12/24/2013, 10/3/2012 (note that month and day do NOT have leading zeros). I would like to convert this variable to date format, but can't seem to find an informat that will work with this. I'm guessing I need to do something like the following.

new_date = input(orig_date, [informat]);

Any ideas?

creating flag if id exists in different dataset

sasprogramming — Wed, 26 May 2021 07:37:52 GMT

If the ID from data set 1 exists in dataset 2, I would like to create a new variable called flag in dataset 2 and it =1 and =0 otherwise. This is shown below. I tried using merge, but couldn't find a good way to do this. any help would be great.

Data 1:

ID	X1	X2
54	9	3
55	3	2
56	2	0
57	4	4
58	4	1
59	6	8
60	1	7

Data 2:

ID	Y1	Y2	Y3
54	55	79	55
55	83	61	55
56	74	77	60
57	64	88	56
60	87	78	83
61	84	87	77
62	78	68	50
63	89	59	52
64	74	77	60
65	55	79	55

Desired data:

ID	Y1	Y2	Y3	FLAG
54	55	79	55	1
55	83	61	55	1
56	74	77	60	1
57	64	88	56	1
60	87	78	83	1
61	84	87	77	0
62	78	68	50	0
63	89	59	52	0
64	74	77	60	0
65	55	79	55	0

Trying to Use Formats to Enhance output

PilOSU — Tue, 25 May 2021 16:19:12 GMT

I am trying to learn SAS and run a basic command as shown in the textbook im readying but it seems to not be working. Below is the code i am running:

libname Jack "C where the file is located";
data survey02;
infile "C: where the file is located.... survey.txt";
input Id Gender $ Age Salary ques1-ques5;
run;


proc print;
run;


proc format;
value $Gender 'M' = 'male'
'F' = 'Female'
' ' = 'Not entered'
other = 'Miscoded';
value Age low-29 = 'Less than 30'
30-50 = '30 to 50'
51-high = '51+';
value $Likert '1' = 'Str Disagree'
'2' = 'Disagree'
'3' = 'No Opinion'
'4' = 'Agree'
'5' = 'Str Agree'
;
run;


title "data set SURVEY with formatted values";
proc print data=survey02;
id ID;
var gender age salary ques1-Ques5;
format gender $gender.
age Age.
Ques1-Ques5 $likert.
salary Dollar11.2;
run;

When i run this i keep getting the error: You are trying to use the character format $LIKERT with the numeric variable ques1 in dataset WORK.SURVEY02. Im confused because im trying to make numeric values into character values so why wouldn't i include $, when i take it out it says: ERROR: The quoted string '1' is not acceptable to a numeric format or informat.

Merging and replace values with two dataset having different number of columns

tmdgus — Tue, 25 May 2021 15:39:04 GMT

Please refer to two fake datasets

Data 1:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4000
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	2980

Data 2:

city	conditonA	conditionB	Value
0	1	2	4100
1	2	3	3000

Final Table:

Year	state	city	conditionA	conditionB	Value
2021	0	0	1	1	5000
2021	0	0	1	2	4100
2021	0	0	1	3	900
2021	1	1	2	1	.
2021	0	1	2	2	3000
2021	0	1	2	3	3000

Basically I want to replace value from data 2 to data 1.

Which method should be used? (data step/proc SQL)

Thanks in advance.,

Count obs in dynamic data set =1 for numeric or not null for characters

Jumboshrimps — Mon, 24 May 2021 14:01:34 GMT

Have some dynamic data sets with different variables each run.

Looking very similar to the below.

Banana_ Cherry Ice Lemon Heath_Bar
ID Split Sundae Swirl Frost Crunch
XCDF1483 1 0 1 0 0
JKTR48392 0 1 0 0 1
OPRT76231 1 1 0 0 1
VXIQ87126 1 0 1 0 0
KTUE61824 0 1 0 1 1

This code appears to be the solution - for an array with variables that are all dates, each variable a different month.

data want;
set have;
_t=put(0,rb8.);
array t(*) months1-months6;/**group your months vars with a double dash*/
want = n(of t(*))-count(peekclong (addrlong(t[1]), 48),_t) ;
drop _t;
run;

My variables are not dates, and the name of the variables are quite different each time.

There would be maybe twenty variables at most.

Result for each ID would be:

XCDF1483 2

JKTR48392 2

OPRT76231 3

VXIQ87126 2

KTUE61824 3

How do I count - and store the total count of non-zero values/non-null values for each unique ID in the data set as a new variable? Not counting "0" or null.

Again, result is stored in the data set as new variable "Total", or something similar.

Didn't use any string examples, but there will be some strings.

Note: A variable is either formatted numeric (0 or 1, nothing else) or a char.

Character variables do not have numbers, and numerics do not have characters.

Longest string variable is 30 letters.

Thanx.

PROC Append - different lengths on BASE and DATA files

su35 — Fri, 21 May 2021 14:46:04 GMT

581 options varlenchk=nowarn;
582 proc append base=varImphsp data=work.bs_varImphsp force nowarn;
583 run;
WARNING: Variable variable has different lengths on BASE and DATA files (BASE 32 DATA 3).

How to remove the warning?

Loop through variables

raivester — Tue, 18 May 2021 00:01:57 GMT

I have variables cnt1 through cnt11. They are numeric (containing values between 1 and 15), but I need to make these variables character. If there is someway to do this without a loop, that would be great; but it seems this may only work with a loop. The following is what I want to work, but I realize this is not how you specify how you iterate through variable names with different numeric suffixes. Does anyone know how to do this?

data want;
	set have

	do i=1 to &max_n_off;
	  ocnt&i. = put(cnt&i., 3.);
	end;

run;

Please can someone help me how to import three different csv files into sas university edition?

Amali6 — Mon, 17 May 2021 21:07:36 GMT

How to create a function in PROC FCMP using information from a dataset?

LilianaRomero — Sat, 15 May 2021 00:11:46 GMT

I have a dataset similar to

Var1	Var2	Date1	Date2	Date3
A	1	1/1/2019	1/16/2020	1/30/2021
B	2	6/15/2018	6/30/2019	7/14/2020
C	2	5/26/2018	6/10/2019	6/24/2020

And I need to create a function that assigns a value depending on the values of a second dataset like the following

Var2	Year	Var3
1	2018	1000
1	2019	2000
1	2020	3000
1	2021	4000
2	2018	5000
2	2019	6000
2	2020	7000
2	2021	8000

The main idea is that if Var1 = A then the function assign the value of Var3 according to Var2 and the year of Date1, if Var1 = B then the function assigns the value in Var3 according to Var2 and the year of Date2 and if Var1 = C then the function assign the value in Var3 according to Var2 and the year of Date3. In order to get the following result:

Var1	Var2	Date1	Date2	Date3	Var3
A	1	1/1/2019	1/16/2020	1/30/2021	2000
B	2	6/15/2018	6/30/2019	7/14/2020	6000
C	2	5/26/2018	6/10/2019	6/24/2020	7000

Thanks a lot for your help!

Comparison between AUC-ROC Curves - Hypothesis test

claudiopcjr — Fri, 14 May 2021 20:50:07 GMT

Hi everyone, I'm running tests to generate comparisions between ROC curves using the PROC LOGISTIC procedure as below:

ods graphics on;
proc logistic data=DATA_CASE_STUDY_ING;
model intodefault = mrating / outroc=troc;
roc; roccontrast;
run;
ods graphics off;

The date 'have' has the column 'defaulted' identifying the customers that presented default (with value 1) or didn't (with value 0) and the column 'rating' that ranks the customers from worst to the better ones.

The main result for me on this procedure is:

By default the ROC1 curve, generated for comparision, has a ROC value of 0.5.

Does anyone know how to change it ? It would be the same as changing the null hypothesis for this test.

I'd like to test it for 0.6, 0.7 and 0.8.

Thanks in advance.

Wildcard?

raivester — Wed, 12 May 2021 15:15:02 GMT

I have variables var1 - var28; there are other variables in the dataset too, like name, race, sex, etc. For the var1-var28 variables, I want to replace any value "other" with "oth." Without listing out all 28 variables, is there a way to do this? (I don't want to list out the variables because (1) there are too many and it is does not seem concise and (2) because this code is automated and every year, we have a different number of these variables--e.g., next year, we may have var1-var35.) In my head I want to do something like the following, but don't know how to execute it in SAS.

if var* = "other" then var* = "oth"

Reordering columns after PROC TRANSPOSE

J_J_J — Wed, 12 May 2021 05:18:00 GMT

Hi!

I'm trying to find the way to sort data after PROC TRANSPOSE automatically

I have data:

data have;
month=1; code='AA1111111'; text='aaa'; prior='2'; type='BB'; num=4; output;
month=1; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=8; output;
month=1; code='BB1111111'; text='bbb'; prior='4'; type='BB'; num=1; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=5134; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=525; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=125; output;
month=2; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=4; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=5321; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=256; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=151; output;
month=3; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=12; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=2860; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=655; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=62; output;
month=4; code='AA1111111'; text='aaa'; prior='2'; type='AA'; num=2; output;
month=4; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=12; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=1962; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=865; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=112; output;
run;
PROC SORT
DATA=have(KEEP= month num code text prior type)
OUT=sorted
;
BY code text prior type;
RUN;

PROC TRANSPOSE DATA=sorted
OUT=want(LABEL="Transposed WORK.WANT")
NAME=Source
LABEL=Label

;
BY code text prior type ;
ID month ;
VAR num;

RUN; QUIT;

and get the result:

code	text	prior	type	Source	4	1	2	3
AA1111111	aaa	2	AA	num	2
AA1111111	aaa	2	BB	num		4
BB1111111	bbb	4	AA	num	12	8	4	12
BB1111111	bbb	4	BB	num		1
CC1111111	ccc	4	CC	num	1962	5134	5321	2860
CC1111111	ccc	4	DD	num	865	525	256	655
CC1111111	ccc	4	EE	num	112	125	151	62

but I need it in the next order and with total:

code	text	prior	type	Source	1	2	3	4	Total
AA1111111	aaa	2	AA	num				2	2
AA1111111	aaa	2	BB	num	4				4
BB1111111	bbb	4	AA	num	8	4	12	12	36
BB1111111	bbb	4	BB	num	1				1
CC1111111	ccc	4	CC	num	5134	5321	2860	1962	15277
CC1111111	ccc	4	DD	num	525	256	655	865	2301
CC1111111	ccc	4	EE	num	125	151	62	112	450

Each month the number of columns will change from 1 to 12 and should be in the order from 1 to 12 (from left to right) and total should be calculated for filled months.

Can you help me please?

Compare two non-nested models

UcheOkoro — Sat, 15 May 2021 14:59:16 GMT

Hello,

Please, I need to compare two non nested models. Please, I need help with SAS code for Monte Carlo simulation in structural equation modeling to do this comparison.

Thank you

Bypassing the LIBNAME/libref

AnnHarper — Sat, 08 May 2021 13:12:48 GMT

Hello,

I teach SAS to MPH students during the summer semester. Last summer, at least one student used the following syntax, which I never knew you could do in 30+ years of SAS programming - but it worked! I'm just wondering, other than not being 'best practice', what the downside of this would be....

PROC SORT DATA= 'C:\zipdataset.sas7bdat' OUT= newdataset; by zipcode; run;

Thanks!

How to Multiple Linear Regression...

Yughaber — Fri, 07 May 2021 05:41:38 GMT

I have data from two different years. The data is about the use of a drug, that is recorded in number of vials, that used to be an uncontrolled drug during the first year and then became controlled during the second year. I want to compare the difference in the drug use during these two time periods of the drug being uncontrolled and controlled. However, I also have other variables such as different types of diseases that the patients had which might be confounding with the amount of the drug used. I also have the different patient counts for each time period which might effect the amount of drug used.

I am thinking of using multiple linear regression for this analysis, however, not quiet sure of the coding part in SAS is it a prog reg or a proc glm? And do I need to combine my data from each year and indicate controlled/uncontrolled in a new column or should I analyze them separately?

I guess I understand how to look for confounding but need more help on how to compare two means while controlling for confounding...

why it doesn't work when i place one variable in the ID statement in proc transpose ?

tianerhu — Tue, 04 May 2021 20:39:11 GMT

proc transpose data = cert.trials out = transtrials1;
var cholesterol triglyc uric;
id name testdate;
run;
proc print data = transtrials1;
run;

the output like this:

proc transpose data = cert.trials out = transtrials1;
var cholesterol triglyc uric;
id name ;
run;
proc print data = transtrials1;
run;

please look at the red part , I place the variable name instead of the two variables 'name' and ' testdate' , and then it doesn't work , why ?

Deal with rounding problem while exporting to CSV

notepadd123 — Tue, 04 May 2021 18:07:27 GMT

Hi,

When export to CSV I have following problem.

My original dataset in SAS before export (Ratio is fixed to $13.9):

Region	Type	Ratio
CAN	Banana	0.234236354
CAN	Apple	0.706008361
CAN	Orange	0.059755285

After exporting to CSV I get:

Region	Type	Ratio
CAN	Banana	0.2342363540200000
CAN	Apple	0.7060083610000980
CAN	Orange	0.0597552850000087

In the original dataset I know the sum is perfectly 1 but after exporting the dataset to csv the sum become

1.0000000000201100

I saw someone mention about floating point in excel CSV before but is there a way that we can avoid the rounding error when export to csv in SAS? Any suggestion will be appreciated!

I've tried "ODS csv file=" and "proc export data=" two methods.

Need help with this

mughaloimran — Tue, 04 May 2021 00:40:18 GMT

For this problem,

Run PROC UNIVARIATE on the variables RBC and WBC from the Blood data set.

1.1 Display the names of the output objects created by this procedure. Attach a screenshot that shows the names.

Show the code and output of names of the output objects. Any unnecessary outputs will cause a penalty of losing points.

1.2 Run PROC UNIVARIATE again, selecting only the output object that shows extreme observations

.And send this output to a temporary SAS data set extrm_obs

. Set the title as “Extreme Observations of RBC and WBC”. Show the code

, data set of extrm_obs and interpretation of the data set. Any

unnecessary outputs will cause a penalty of losing

points.

1.3 Issue two ODS statements: one to select the Basic Statistical Measures output object and the other to send this output to a permanent SAS data set : RBC_WBC_Measures. Set the title as "Basic Statistical Measures for RBC and WBC". Show the code ,data set and interpretation of the dataset

Is this code correct?

data Blood;
set blood;
run;

ods trace on;
proc univariate data=blood;
var RBC WBC;
run;
ods trace off;
ods select quantiles;
proc univariate data=blood;
var RBC WBC;
run;

ods select extrm_obs;
title "Extreme Observations of RBC and WBC";
proc Univariate data=Blood;
id Subject;
var RBC WBC;
run;
ods select extremeobs;
title "Extreme Observations of RBC and WBC";
proc Univariate data=Blood;
id Subject;
var RBC WBC;
run;

how to delete the first row ?

tianerhu — Mon, 03 May 2021 23:14:34 GMT

title 'class';
proc print data = cert.class ;
run;

proc transpose data = cert.class out = score_transposed;
var name score1-score3 homework ;
run;

proc print data  = score_transposed ;
 title 'score for the year';
run;

The following is output :

If I want to delete the first row in the second table—— _NAME_ COL1 COL2 COL3 COL4 , how to do ?

error 73-322 when using proc causalmed for survival analysis

aw016 — Mon, 03 May 2021 06:17:19 GMT

I'm trying a SAS example from the SAS help on my laptop. It basically tells how to use proc causalmed for survival analysis. However, I kept getting "error 73-322: Expecting an =." under "weeks*censor(1)".

I also tried my own code for the same analysis and got the same error.

Here is the link from SAS help web about proc causalmed with survival analysis:

https://documentation.sas.com/doc/en/statug/v_003/statug_causalmed_examples05.htm

Does anyone know how to fix it or do survival analysis in proc causalmed? I really appreciate it!

Below is the example code:

proc causalmed data=RedDye ciratio=log poutcomemod pmedmod;
   class RedDye Female Tumor;
   model Weeks*Censor(1) = RedDye | Tumor / aft;
   mediator Tumor(event='1') = RedDye(treat='3');
   covar Female;
   freq Freq;
run;

How to run proc reg with a subsample without creating a subsample?

Phil_NZ — Sun, 02 May 2021 22:08:20 GMT

Hi all SAS Users,

Have a nice week,

Today I want to run PROC REG with a subsample, I know one way is to create a subsample and run PROC REG, but I wondering if there is any way to run directly in PROC REG procedure without another data step for creating a subsample.

My code of running for the WHOLE sample as below:

proc reg data=merge_treat_con_copy;
	model wINV_DAY= pt wFIRM_SIZE LNGDP UNEMPLOYMENT  INFLATION 
		wTANGIBILITY FCF wLOG_MVE wCAP_INT wGRO_MAR/ tol vif collin;
run;

Now I want to run where INDC3="RTLS".

Can you please hint me to sort it out?

Warmest regards and thanks,

Phil.

Military Time

SriCh1 — Sun, 02 May 2021 21:06:26 GMT

I have an excel sheet where the data set has columns with military time but no separators. How to convert the military time to hh: mm. For example, the columns have military time like 1509,7,30,637.

Is there anything wrong with this question?

tianerhu — Sun, 02 May 2021 20:50:41 GMT

Can we read the data of the variable Department from ?

ERROR: File DBO.prop_FinalEstimates.DATA does not exist.

nicole_e_allen — Fri, 30 Apr 2021 18:40:31 GMT

I am working on a project at work and using the following code:

libname CP odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claims-claimsproperty-01 ;

DATABASE=ClaimsProperty; TRUSTED_CONNECTION=YES" schema=PD;

LIBNAME PD ODBC NOPROMPT="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claims-claimsproperty-01 ;

DATABASE=ClaimsProperty; TRUSTED_CONNECTION=YES" SCHEMA=PD;

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCntrlRpt; TRUSTED_CONNECTION=YES" schema=dbo;

libname DBO odbc noprompt="DRIVER={ODBC Driver 17 for SQL Server}; SERVER=mss-p1-claimscontrolbi-01 ;

DATABASE=BISSClaimsCmnDmnsn; TRUSTED_CONNECTION=YES" schema=dbo;

PROC SQL;

CREATE TABLE Estimates AS

SELECT E.ClaimYear,

E.ClaimNbr,

E.PropNbr,

E.PropId,

PL.FinalEstimateAcctMonth,

E.FinalEstimateVehicleModelYear,

E.FinalEstimateVehicleMakeDesc,

E.FinalEstimateVehicleModelDesc,

F.FacilityId,

F.FacilityStateCode,

F.SWEShopInd

FROM PD.Estimate as E

LEFT JOIN PD.Date as D

ON E.FinalEstimateCompleteDateId = D.DateId

LEFT JOIN PD.Property as P

ON E.ClaimYear = P.ClaimYear

AND E.ClaimNbr = P.ClaimNbr

AND E.PropNbr = P.PropNbr

LEFT JOIN PD.PropertyPlus as PP

ON E.ClaimYear = PP.ClaimYear

AND E.ClaimNbr = PP.ClaimNbr

LEFT JOIN PD.Inspection as Ins

ON E.PropId = Ins.PropId

LEFT JOIN PD.Facility as F

ON Ins.FacilityId = F.FacilityId

LEFT JOIN PD.PartsLaborShopSummary as PL

ON E.PropId=PL.PropId

LEFT JOIN PD.Repair as R

ON E.PropId=R.PropId

WHERE PL.FinalEstimateAcctMonth > 202003 and PL.FinalEstimateAcctMonth < 202102

AND P.TotalLossInd = 0

AND P.PropTypeDesc = 'Vehicle'

AND E.TotalEstimateLineCnt >= 50

AND F.FacilityStateCode in ('IA','MI','UT')

AND F.SWEShopInd = 1;

%LET INFILE = \\prog1\east\AppsProd\neclaims\Nicole Allen\RFP Project\RFP Project Shops.xlsx;

PROC IMPORT OUT= ExcelFacilityName

DATAFILE= "&INFILE."

DBMS= XLSX REPLACE;

GETNAMES= Yes;

RUN;

PROC SQL;

CREATE TABLE RepairFacilityNames AS

SELECT A.*, B.*

From Estimates AS A

LEFT JOIN ExcelFacilityName AS B

ON B.FacilityId = A.FacilityId;

QUIT;

/** Repair Time Query **/

PROC SQL;

CREATE TABLE RepairTime AS

Select DISTINCT C.*,

sum(FE.RepairLaborHrs) as EstRepairTime

from RepairFacilityNames AS C

left join DBO.PROP_FinalEstimates as FE

ON A.PropId = FE.PropID

where FE.ReportingAcctMonth >= 202003 and FE.ReportingAcctMonth <= 202102

group by FE.EstState,FE.RepairFacilityName,FE.claimrefnbr

order by FE.EstState,FE.RepairFacilityName,FE.claimrefnbr;

QUIT;

The very last PROC SQL statement I keep getting the error: ERROR: File DBO.prop_FinalEstimates.DATA does not exist

I get no other errors in my log. Thoughts?

PROC BOXPLOT wont correctly display Mean values using INSETGROUP

Rover17 — Fri, 30 Apr 2021 15:47:04 GMT

Hi SAS Community,

When I run my code below to create a set of boxplots, I use the INSETGROUP statement to insert Min, Mean, and Max values.

The Min and Max values display as requested, but the Mean values don't.

Each Mean value has 3 numbers before a decimal, then 2 numbers after the decimal.

Sincere there are 15 boxplots in the chart, I don't think there is enough space to show the Mean values for all the boxplots. Some boxplots show an asterisk for the Mean value.

How can I adjust the spacing or request truncated mean values for each of the boxplots in the chart?

proc boxplot data=boxplot_3;
plot Step1_1*Program ;
inset min mean max /
header = 'Overall Statistics'
pos = tm;
insetgroup min mean max /
header = 'Min & Max Values for Each Program';
label Step1_1 = 'Step 1 Scores';
run;

Thank you,

Aaron

SQL: Merging ranges. Left join?

BruceBrad — Fri, 30 Apr 2021 07:50:43 GMT

I'm trying to merge two files which have start and end dates on each record. I want an output file which has all combinations of date spells. Where one file does not have data for a given range, I want to return missing values for its variables.

I almost have this working, but my code doesn't return the desired missing values. (The last record in the desired file in the code). Do I need to make this a left join? (I'm not very familiar with SQL).

data data1;
input start end v1;
if _n_>1 and start^=lag(end)+1 then error;
datalines;
1 2 1.12
3 4 1.34
5 5 1.55
6 7 1.67
8 8 1.88
;
run;

data data2;
input start end v2;
if _n_>1 and start^=lag(end)+1 then error;
datalines;
1 2 2.12
3 3 2.33
4 4 2.44
5 7 2.57
;
run;

* Desired merged file;
data desired;
input start end v1 v2;
datalines;
1 2 1.12 2.12
3 3 1.34 2.23
4 4 1.34 2.44
5 5 1.55 2.57
6 7 1.67 2.57
8 8 1.88  .
;
run;

* get list of time spells;
data long1;
set data1 data2;
keep start end;
run;
proc means data=long1 (keep=end) noprint nway;
  class end;
  output out=long2 (drop=_:) ;
run;
data spells;
set long2;
start = lag(end)+1;
if start=. then start=1;
run;

proc sql;
   CREATE TABLE merged AS
   SELECT s.start, s.end, v1, v2 
   FROM spells as s, data1 as d1, data2 as d2
   where d1.start <= s.start and d1.end >= s.end and
         d2.start <= s.start and d2.end >= s.end ;
   ;
QUIT ;

Proc Boxplot is not showing all boxplots in a single chart on a single page. How can I change this?

Rover17 — Wed, 28 Apr 2021 18:45:17 GMT

Hi SAS Community,

I'm using the following code to create a chart of boxplots. In my dataset, I have a continuous variable called Step1_1. I also have a categorical variable called Program. There are 30 Programs, and each Program is to have a boxplot.

I want to create a single chart that shows the 30 boxplots all displayed on a single page. I'm using ODS to output the chart in a RTF document. The orientation is landscape.

The problem I have is that Proc Boxplot is splitting the chart to display 15 boxplots on the first page and the remaining 15 on the second page.

proc boxplot data=boxplot_3;
plot Step1_1*Program ;
inset min mean max /
header = 'Overall Statistics'
pos = tm;
insetgroup min max /
header = 'Min & Max Values for Each Program';
label Step1_1 = 'Step 1 Scores';
run;

Thanks for your help !!

calculate the frequency of positive and negative values by id

sasphd — Mon, 26 Apr 2021 15:17:53 GMT

Hello,

I have this table

id variation

1 0.258

1 -0.53

2 0.563

2 -0.386

2 -0.568

I want to calculate the number of negative and positive variations by id. the table that I want

id variation count

1 positive 1

1 negatif 1

2 positive 1

2 negatif 2

How do I retrieve ResponsibleParties for Report.BI Objects

_Dan_ — Mon, 26 Apr 2021 09:38:23 GMT

Hi,

I've found a code snippet online to get most of what I require in regards to Report.BI Metadata properties, however, the code to get ResponsibleParty information was answered elsewhere in a separate context, and I can't cobble together the two pieces of code.

My issue is understanding how to utilise the metadata_getnasncommand and whether it's right to do so within the getnobj loop. The query does return two usernames based on my ask, but, it can return those 1...2...even 7 times over. Same record, just many duplicates.

Does anyone know (specifically regarding ResponsibleParties) how I obtain the creator/last modified names and dates, please?

data MDIDs (drop=_: label="SAS VA Report List");
  length id $17 _uri name _name _modified _created location _location createdby modifiedby name person $256; 
  length created modified 8; 
  format created modified datetime19.;
  _nobj=1; 
  _n=1; 
  call missing(id, _uri, _uri2, name, role, person, createdby, modifiedby, _name, _modified, _created, _location);

  do while(_n le _nobj);
    _nobj=metadata_getnobj("omsobj:Transformation?@PublicType = 'Report.BI'",_n,_uri);
    _rc=metadata_getattr(_uri,"Id",id);
    _rc=metadata_getattr(_uri,"Name",_name);
    _rc=metadata_getattr(_uri,"MetadataCreated",_created);
    _rc=metadata_getattr(_uri,"MetadataUpdated",_modified);

	created=input(_created,anydtdtm.);
    modified=input(_modified,anydtdtm.);
	name=translate(translate(translate(translate(_name,"-","–"),"£","£"),"%","%"),"+","+");

	/*	Get creator / last modified. Doesn't work properly.	*/
	passn=metadata_getnasn(_uri,"ResponsibleParties",1,Respuri);
	_rc=metadata_getattr(Respuri, "Name", CreatedBy);
	output;
	passn=metadata_getnasn(_uri,"ResponsibleParties",2,Respuri);
	_rc=metadata_getattr(Respuri, "Name", ModifiedBy);
	output;

	* Get folder object the current Report is in *;
    _rc=metadata_getnasn(_uri,"Trees",1,_uri);
    * Get folder name the current Report is in *;
    _rc=metadata_getattr(_uri,"Name",location);
    _tree=1; 
    * Loop up the folder hierarchy *;
    do while (_tree>0);
      * Get the parent folder object *;
      _tree=metadata_getnasn(_uri,"ParentTree",1,_uri);
      if _tree > 0 then do; 
        * If there was a parent folder, get the name *;
        _rc=metadata_getattr(_uri,"Name",_location);
        * Construct the path *;
        location=catx('/',_location,location);
      end; 
    end; * Folder Hierachy *;

    location = '/'||location;

    output; 
    _n=_n+1; 
  end; 
run;

when i use 'proc export' to have a 'csv file' , why I have 'excel file' instead of 'txt file' ?

tianerhu — Wed, 21 Apr 2021 21:54:18 GMT

when i use 'proc export' to have a 'csv file' , why I have 'excel file' instead of 'txt file' every time?

Could you tell me how to get directly a 'txt file' ? Thank you .

Split a character variable into a character and a date mmdd

KayeMcK — Wed, 21 Apr 2021 14:29:44 GMT

I have a file where my date came in as a character and I need to make it a date to use it in Forecasting.

My variable date looks like this:

Friday, April 8

Saturday, April 9

Sunday, April 10

...

What code would I use to make a character variable for DOW and a second variable for the date mmdd?

Thanks,

K-

What is the size (in bytes) of the CSV file that I exported?

tianerhu — Tue, 20 Apr 2021 21:20:57 GMT

The question ask for the size of the CSV file that I export ? Can I find that in log or anywhere else ?

I can find the size in log .

why the output file is like this ?

tianerhu — Tue, 20 Apr 2021 20:08:56 GMT

proc export data = work.flightempdata
 outfile = "C:\Users\li\Documents\My SAS Files\prep guide\base\practice data\cert\flightempdata.csv"
 dbms = CSV
 replace;
run;

additionally, the four value from the second to fifth of the column date is missing . why?

the answer is like following:

Large dataset: Creating one variable (column) from multiple years of data

EMc — Sat, 17 Apr 2021 23:27:04 GMT

I have 180 columns containing the same character data reported each year over many years (from a longitudinal data set). I am looking to combine these columns into one singular column so that I can compute two way frequencies etc. I have looked into "append" however I understand this is for datasets not variables. I have also tried a transpose, however I end up with 9000 columns (number of patients). Other similar posts suggest an array for this type of data, however they all require you to enter datelines. My dataset is much too large to record each individual dateline! Hope someone has insight as to how to do this, or possibly a better method!

Current Data:

Patient ID Year 1. Year 2. Year 3. Year 4. Year 5

1. ABC ABB ABA ABC BAC

2 ACA ABC ABB ABA AAC

3 ABB ABA ACA ABC AAC

What I want:

Patient ID Health Condition

1. ABC

1. ABB

1. ABA

1. ABC

1. BAC

2. ACA

2. ABC

2. ABB

2. ABA

2. AAC

3. ABB

3. ABA

3. ACA

3. ABC

3. AAC

Moving average with proc expand excluding the current observation

Bright — Sat, 17 Apr 2021 05:05:58 GMT

Hi,

I am trying to use proc expand to calculate moving average of for example 3 last observations excluding the current observation. Suppose the data is like this:

ID Price

1 10

2 0

3 20

4 50

Using this code

proc expand data=have out=want method=none;
convert price=price_avg / TRANSFORMOUT=(movave 3);
run;

I get

ID Price Price_avg

1 10 10

2 0 5

3 20 10

4 70 30

Now, consider observation 4, I want the moving average does not consider the current observation. So the Price-avg should be (20+0+10)/3=10. Is there any way to do that directly in the proc expand?

Thanks

Create a comprehensive dataset

RDellaVilla — Thu, 15 Apr 2021 14:37:50 GMT

Hello,

I am having an issue with a procedure I need for a research application.

I am dealing with several datasets, each for a given year, since 1996 to 2020. These datasets are composed of the same columns. Namely, I have a series of files named optionm.opprcdyyyy with yyyy ranging from 1996 to 2020.

All I want to do is to create a unique file listing all of the annual datasets together.

I have then to apply some filters. I think that by creating a unified document I could much more conveniently apply my filters.

I am aware this is quite trivial, but I have been stuck on this for quite a long time.

Could anybody help?

Thanks

plotting same criterion, two predictors

emaneman — Thu, 15 Apr 2021 06:58:30 GMT

Dear community,

I have a dataset with three variables, in which I ran a multiple regression model with one criterion/RMET and two predictors LIT and POP.

I would like a scatter plot + regression line for both predictors, in the same graph.

So far I have been unable to do so using REG or SGPLOT. The latter allows to have grouped data, so that if I wanted to plot, say, the RMET/Literary regression line for men vs. women, I could easily do so, and even color them differently. But that is not what I want.

I would like one graph with RMET in the y axis, and both LIT and POP on the x axis (they are both standardized vars), with say a blue scatter and reg line for LIT and a red scatter and reg line for POP.

I attach the datafile in excel format.

Thanks in advance.

Eman

Suggestions?

Proc Import: Can I suppress "NOTE: VARCHAR data type is not supported by the V9 engine."

BruceBrad — Thu, 15 Apr 2021 05:51:50 GMT

I'm using proc import to read in XLSX files. I get lots of these notes in the log. Can I suppress them without turning all notes off?

Infinite scroll via proc http

JAR — Sun, 11 Apr 2021 03:44:57 GMT

I am trying to get data (nearly 4000 rows) from a page, which has an infinite scroll. Proc HTTP reads only the first 100 rows.

Could someone please help me to solve this!

JAR

%scan function

marathon2 — Wed, 07 Apr 2021 17:19:28 GMT

I am trying to set start_yr to be 2021 and end_yr to be 2022, using the macro below. I am a beginner in SAS programming language. Can someone help?

%macro storms(yr);
%let start_yr = %scan(&yr,1);

%let end_yr = %scan(&yr,2);
%put &=start_yr;

%put &=end_yr;
%mend storms;

storms(2021 2022)

converting dataset to format

NJGIRL — Wed, 07 Apr 2021 14:01:05 GMT

Hi all,

Not a newbie, just can't find my code that converts a sas dataset into a format via Proc Format.

Anyone have the straightforward code readily available?

Thanks so much!

dealing with unknown characters

rhaley1821 — Tue, 06 Apr 2021 20:27:51 GMT

Hi all,

I am a new sas user trying to clean up a dataset. I am interested in coding some categorical variables into a composite variable of water and sanitation quality.

I have a dataset from nicaragua where responses have accents. It was a previous SPSS file, and when I import to SAS all accent characters convert to unknown characters and prevent me from running a proc freq. I simply need 9 categories to code as 0/1, so the variable could simply be converted to numeric if I know what the values mean. Can someone please advise on how to get rid of the unknown character? The variable in question is S1P15

dataset is attached. Current proc import below:

*import spss dataset and convert;
proc import datafile = "/folders/myfolders/sasuser.v94/WFP/datasets/EMNV14-02 DATOS DE LA VIVIENDA Y EL HOGAR (1).SAV"
out= work.nicaragua
dbms=sav
replace;
run;

Thank you!

Problem with PROC RANK

elif — Tue, 06 Apr 2021 19:28:13 GMT

Hello,

In the “allvar” dataset, variables divi, rd, and sin take values of either 0 or 1; variable divo takes values -1 or 0. I am using this data set to create portfolios for each date (newdatadate in my case).

I am using PROC RANK and group them into 5 before creating portfolios.

My Code:

OPTIONS MPRINT SYMBOLGEN;

%LET avars = divi divo rd sin ; * Note: (I have more than 60 variables here);

%LET n_avars=%SYSFUNC(countw(&avars));

%PUT &n_avars;

%LET grp = 5;

PROC SORT DATA = allvar;

BY newdatadate;

RUN;

PROC RANK DATA=allvar OUT=allvar_ranked group=&grp;

BY newdatadate; VAR &avars;

RUN;

I think, in the allvar_ranked dataset, variables divi, divo, rd, and sin should have only two different ranked values since they already had taken two values before they were ranked. However, it’s not the case.

When I run the following (before running proc rank):

proc sql; select distinct divi from allvar; quit; * Values: ., 0, 1 ;

proc sql; select distinct divo from allvar; quit; * Values: ., 0,-1 ;

proc sql; select distinct rd from allvar; quit; * Values: 0, 1 ;

proc sql; select distinct sin from allvar; quit; * Values: ., 0, 1 ;

When I run the same in the ranked dataset:

proc sql; select distinct divi from allvar_ranked; quit; * Ranked Values: ., 0, 2, 3, 4 ;

proc sql; select distinct divo from allvar_ranked; quit; * Ranked Values: ., 0, 1, 2, 3 ;

proc sql; select distinct rd from allvar_ranked; quit; * Ranked Values: 1, 2, 4 ;

proc sql; select distinct sin from allvar_ranked; quit; * Ranked Values: ., 2, 4 ;

I was using this command previously and have never encountered this problem.

How can I fix this?

Thank you!

How to identify "missing" replications for each condition?

SAS-questioner — Thu, 01 Apr 2021 20:43:42 GMT

Thank you for reviewing my questions, I am really stuck in here.

I have a set of data that I have 64 conditions and 500 replications for each condition. However, some conditions have less than 500 replications, and it is hard for me to identify the conditions with less than 500 replications and how many are missing because there is no missing values (such as "." or blank) in replications. For example, the rep is like this: 1, 2, 4, 5, 8, 9, 10, ......., 500. (After 2, the replications directly jump to 4, and after 5, the replications directly jump to 😎

Could anyone tell me how to identify the conditions with less than 500 replications and how many of replications are missing for each condition? Thank you!! The data format is like below:

var1 var2 var3 var4 rep

a b c d 1

a b c d 2

a b c d 4

a b c d 5

a b c d 6

......

a b c d 500

a b c e 1

a b c e 2

.....

a b c e 500

a b f d 1

a b f d 3

......

a b f d 500

;

The var1, var2, var3, and var4 are manipulated design factors, the different combination of design factors is one condition, and each condition has 500 reps. (like the combination of 'a,b,c,d' is condition 1, 'a,b,c,e' is condition 2, etc., ) Hopefully I made my questions clear. Thank you again!!

Sum with retain

aguilar_john — Wed, 31 Mar 2021 14:24:10 GMT

Hello everyone,

I have a problem which I have already solved more or less but was wondering if there might be a more elegant/efficient/faster solution to it. Hope somebody can help me.

DATA have; format month date9.; input month date9. id amount; datalines; 31DEC2020 1 10 31JAN2021 1 0 28FEB2021 1 5 31MAR2021 1 0 31DEC2020 2 0 31JAN2021 2 0 28FEB2021 2 5 31MAR2021 2 0 31DEC2020 3 20 31JAN2021 3 10 28FEB2021 3 0 31MAR2021 3 0 ;

This is the table I have and I want to have a table which gives me for each month the overall sum of the amounts.

I tried it with this:

DATA want; SET have; BY id; RETAIN sum_all; sum_all = sum(amount, sum_all); RUN;

But even though I group by the id the cum calculation does not restart at every new id.

I solved it by sorting the have table and the doing the calculation by introducing two IF conditions:

proc sort data=have out=have_sorted ; by id month ; run; DATA want2; SET have_sorted; BY id; RETAIN sum_all; IF first.id AND amount GT 0 THEN sum_all = 0; IF first.id AND amount EQ 0 THEN sum_all = amount; sum_all = sum(amount, sum_all); RUN;

So the table want2 is how I want the end result to look like, for each id and month sum of amount so far.

Thank you for your help.

Loops for Read In

raivester — Fri, 26 Mar 2021 15:42:30 GMT

Apologies for double-posting; however, I don't think I was clear enough in my original post--there is more nuance to my problem, I think.

My first issue, is that I am trying establish libnames for a series of folders. I would like to do this in a loop and using a macro. The macro is year = 2020, but some of the year indications in the filepaths are the full four-digit year YYYY and others are the two-digit year YY. This is an established program and it is best to only use the existing macros. (I do realize it would be easiest to create a macro equal to the two-digit year.) What I have is what is below, but I'd like to condense it:

%let lastyear = 2020;        

libname  data11"S:\Projects\Data\2011\Intermediate";
libname  data12"S:\Projects\Data\2012\Intermediate";
libname  data13"S:\Projects\Data\2013\Intermediate";
libname  data14"S:\Projects\Data\2014\Intermediate";
libname  data15"S:\Projects\Data\2015\Intermediate";
libname  data16"S:\Projects\Data\2016\Intermediate";
libname  data17"S:\Projects\Data\2017\Intermediate";
libname  data18"S:\Projects\Data\2018\Intermediate";
libname  data19"S:\Projects\Data\2019\Intermediate";
libname  data20"S:\Projects\Data\2020\Intermediate";

Any thoughts?

My second goal is to improve the efficiency of reading in a set of data sets in a set statement. Again, these have a consistent naming convention and again I am working with a macro that is four digit year of the most recent year of data.

	%let lastyear = 2020; 

        data new_data;
	  	  set   data18.file_name(in=_18)
			data19.file_name(in=_19)
			data20.file_name(in=_20);

	  new_var=old_var(mn, dy, yr);
	run;

Read In Using Loop

raivester — Fri, 26 Mar 2021 14:17:03 GMT

I am wondering if there is a more efficient way to append a bunch of data sets during read in. Fortunately, all the data sets I am working with follow the same naming convention and only differ in a single number that increments each year. I have been doing the below, but since is this something I will simply have to add to each year, am wondering if there is a way to use a macro (indicating the most recent year) and a loop to make this more efficient.

data app_data;
	  set 	ds2015
			ds2016
			ds2017
			ds2018
			ds2019
			ds2020;

        *Other stuff happens;
run;

Proc Export as .txt from SAS EG to Local Machine

gflowers — Wed, 24 Mar 2021 13:56:13 GMT

Hello,

I'm trying to export data as .txt (comma delimited) to a local machine using SAS EG.

A couple questions:

1) Is this even possible? While looking through the documentation, it seems that you can only specify the DBMS as Accesscs or Excelcs (link) in order to export to a local machine. However, when I perform an export as .txt through the "Export file as step in project" it works fine.

2) Would you have to specify a Server_Name and Port_Number similar to an Excelcs export?

PROC EXPORT 
DATA=WORK.DISTRIBUTIONS
OUTFILE="<file path>"
DBMS=<?> REPLACE;
SERVER_NAME = "&serv_name";
PORT_NUMBER = 9621;
RUN;

Any help is appreciated. Thanks.

All observations have the same response. No statistics are computed.

u49589061 — Fri, 19 Mar 2021 06:54:11 GMT

Filename Refile '/home/u49589061/MPBH 421/Programs/condom.csv';

Proc import Datafile=Refile
	DBMS=CSV
	Out=work.condom;
	Getnames=YES;
Run;

Proc contents data=work.condom;
Run;

Proc print data=work.condom;
Run;


Data condom1;
Set work.condom;
If gender="male" then gender=0;
Else gender=1;
If use="unprotected" then use=0;
Else use=1;
If safety gt 5 then delete;
If sexexp gt 10 then delete;
If previous="Condom us" then previous=0;
If previous="No condom" then previous=1;
Else previous=2;
If selfcon gt 9 then delete;
If perceive gt 6 then delete;
Previous1 = previous EQ 1;
Previous2 = previous EQ 2;
Run;
*****************************************************;
Proc logistic data=condom1;
class gender/param=ref;
Model use(ref=last)=perceive safety gender/expb cl rsq;
Output Out=condomres1 pred=pred;
Run;

PROC LOGISTIC DATA=condom;
Class gender/param=ref;
MODEL  use (REF="0")=  perceive safety gender previous1 previous2 selfcon sexexp /EXPB CL RSQ;
OUTPUT OUT=condomres2 PRED=pred   ;
Test previous1=0,  previous2=0,  selfcon=0,  sexexp=0;
Run;

- I need some major help for the following two parts:

First, I'm trying to make dummy variables for the variable: previous Previous1 = previous EQ 1;
Previous2 = previous EQ 2:

Second, I am currently running into problems with running the last two proc logistics statements. After I run the first proc logistic statement, it states that "All observations have the same response. No statistics are computed." And then when I run the second proc logistic statement, it states that the "variable previous1 was not found."

Adding to total row to a numeric group value in Proc Report

Batman — Thu, 18 Mar 2021 17:39:00 GMT

I'm trying to add total row header and received an error messages because the Group variable is numeric. Can anyone recommend a solution?

26 data tab1;
27 input c $ year benes death;
28 list;
29 datalines;

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
30 a 2009 1 1
31 a 2010 1 0
32 a 2009 1 0
33 a 2010 1 1
34 a 2010 1 0
35 a 2010 1 0
36 b 2009 1 1
37 b 2010 1 0
38 b 2009 1 0
39 b 2010 1 0
NOTE: The data set WORK.TAB1 has 10 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

40 ;

41 run;
42
43 proc format;
44 value dec
45 0='No'
46 1='Yes'
47 ;
NOTE: Format DEC is already on the library WORK.FORMATS.
NOTE: Format DEC has been output.
2 The SAS System 11:28 Thursday, March 18, 2021

48 value missnum
49 . = '0'
50 other=[comma8.]
51 ;
NOTE: Format MISSNUM is already on the library WORK.FORMATS.
NOTE: Format MISSNUM has been output.
52 value missdec
53 . = '0'
54 other=[8.2]
55 ;
NOTE: Format MISSDEC is already on the library WORK.FORMATS.
NOTE: Format MISSDEC has been output.
56 value misspct
57 . = '0.0%'
58 other=[percent7.1]
59 ;
NOTE: Format MISSPCT is already on the library WORK.FORMATS.
NOTE: Format MISSPCT has been output.
60 run;

NOTE: PROCEDURE FORMAT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

61
62 proc report data=tab1 split='~';
63 columns c year benes=benessum ('deceased' death, benes) death=decpct
64 ('rate' death, benes=rate1) benes=rate2 ('increase likelihood~of disenrollment' likelihood);
65 define c /group ;
66 define year / group;
67 define benessum / sum 'Benes' format=missnum.;
68 define death / across '' format=dec. missing;
69 define benes / analysis '' sum format=missnum.;
70 define decpct / analysis 'pct deceased' mean format=misspct.;
71 define rate1 / analysis '' pctsum format=misspct.;
72 define rate2 / analysis 'pct of cohort' pctsum format=misspct.;
73 define likelihood / computed ''
74 format=missdec.;
75 break after c / summarize;
76 rbreak after / summarize;
77 compute after c;
78 year = 'Total';
79 endcomp;
80 compute after;
81 c='Total';
82 endcomp;
83 compute likelihood;
84 likelihood=_c8_/_c7_;
85 endcomp;
86
87 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
1:9
NOTE: Invalid numeric data, 'Total' , at line 1 column 9.
NOTE: Invalid numeric data, 'Total' , at line 1 column 9.
NOTE: Missing values were generated as a result of performing an operation on missing values.
3 The SAS System 11:28 Thursday, March 18, 2021

Each place is given by: (Number of times) at (Line):(Column).
1 at 1:17
NOTE: There were 10 observations read from the data set WORK.TAB1.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.32 seconds
cpu time 0.03 seconds

Test difference in slopes

ahschnell — Thu, 18 Mar 2021 01:02:01 GMT

Hi. I've run a segmented regression for count data in GENMOD. I created 2 slopes and I want to statistically test the difference between the 2 slopes. Slope1=Slope2 or Slope1-Slope2=0.

I can do this easily in STATA using the lincom command, but I can't figure out how to do it in SAS. TIA

Proc import : language issues

Liamb — Thu, 11 Mar 2021 18:42:15 GMT

Hello,

I imported an excel file with a column containing Kazakh (+++) and other languages (Russian, English) and I get this warning message "Some character data was lost during transcoding in column". My column values beyond those in English have all been changed. How can I solve this issue?

Thanks

Best regards,

Correct procedure for cross tab with headings only

MillerEL — Thu, 11 Mar 2021 16:43:53 GMT

Hello,

Can someone help me determine the best procedure for creating a crosstab with variable names instead of counts/sums/etc.

For Example, I can do a PROC FREQ of _Region* Area to get this output:

	Area1	Area2	Area3	Area4
Region1	0	4	1	0
Region2	5	2	0	0
Region3	3	0	0	6

But what I want is this:

Region1	Area2	Area3
Region2	Area1	Area2
Region3	Area1	Area4

I have 15x115 variables, and the first output is too cumbersome and requires a lot of Excel manipulation.

Is there a procedure/option/format that will help me to create an output that looks like the second?

I'm certain I must be overthinking this, but I'm just stuck.

Thank you!

proc compare, ony output unequal obs

Jaheuk — Thu, 11 Mar 2021 16:42:22 GMT

when I use proc compare to compare 2 data sets AND everything is 100% the same,

I still get output because I use the ID statement

how can I avoid this??

Calculate percentage of total for each category in SAS

xliu1 — Mon, 08 Mar 2021 20:56:38 GMT

I have a table in SAS like the one in screen shot. I need to add one more column to show the percentage of sub-total for each category. For example, class_type='1-LOWER UG' and Campus='1-TPA', the percentage (using CY column) would be 7368/42008 = 17.5%; class_type='2-UPPER UG' and Campus='1-TPA', the percentage (using CY column) would be 22801/42008 = 54.3%.... Is there a simple way to do this? Will appreciate any help! Thanks!

proc report - merging headers (and columns) with across and grouped variables

RJB_NZ — Mon, 08 Mar 2021 04:43:13 GMT

Hi,

My code runs, yay! But doesn't get me the desired results...

I currently get:

But I'd like the top two header rows put into one.

And I'd like the last two of those column headers to be in red font and merged across 2 cells, to line up with the columns below them.

Like this:

I know that's a few things together, but I'm worried that they'll interact and so can't be considered separetly.

Thanks

Rodney

%let PathAndFile = 'c:\MyFolder\Shoes Test.xlsx';
data shoes_raw;
	set sashelp.shoes;
	where product in ('Sandal' 'Slipper' 'Sport Shoe');
run;

ODS excel 
	file = "&PathAndFile."
	options (	embedded_titles='OFF' /*On means text in TITLE statement of proc tabulate will show in excel file*/
				sheet_interval="none" /*when to split off to a new page - "none" means not at all*/
				sheet_name='Shoes Tests' /*Name of Sheet*/
				gridlines ='off' /*set printable gridlines*/
				absolute_column_width='23, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10' /*setting column widths*/
				absolute_row_height='15.75' /*setting column height*/
			);


	proc report data = shoes_raw missing;

		*stating columns to be in the report and their order;
		column region ('Products sold' stores,product)
					  ('Sales amount' Sales,product)
					  ('Sandal short' Sandal_Diff)
					  ('Shoe short' Shoe_Diff)
			    ;

		*define columns directly from dataset variables;
		define region  / 'Global region' group
								 style(header)=[just=left borderbottomstyle=solid bordertopstyle=solid];
		define stores  / ' ' analysis sum format=comma.
							   style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid  tagattr='MERGEACROSS:2'];
		define Sales   / ' ' analysis sum format=dollar.2
							   style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid  tagattr='MERGEACROSS:2'];
		define product / ' ' across order=internal
								 style(header)=[borderbottomstyle=solid bordertopstyle=solid just=right.];
		*define cumputed columns;
		define Sandal_Diff / "Slipper - Sandal" computed format=dollar.2
							   style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid  tagattr='MERGEACROSS:2']
							   style(column)=[ tagattr='MERGEACROSS:2'];
		define Shoe_Diff / "Slipper - Sport Shoe" computed format=dollar.2
							   style(header)=[just=right borderbottomstyle=solid bordertopstyle=solid  tagattr='MERGEACROSS:2']
							   style(column)=[tagattr='MERGEACROSS:2'];


		*computing columns (that are based off other columns);
			*columns produced from the across option of the 'month' variable do not have a name so must be reffered to by their column number, as can be counted in the column statement;
		compute Sandal_Diff;
			Sandal_Diff = _c6_ - _c5_;
		endcomp;
		compute Shoe_Diff;
			Shoe_Diff = _c6_ - _c7_;
		endcomp;
	run;

ods excel close;

Proc Transpose and keep the first column

PauloC — Sat, 06 Mar 2021 01:15:32 GMT

Hello! I am trying to transpose a table keeping the first column. I would appreciate help! Thank you in advance!

I have this data set:

ID	Abrv	A	B	C	D
35	AE	284	5143.03	234	4692.75
35	AI	136	1450.67	110	1031.6
43	AE	207	4389.86	93	1890.86
43	AI	130	1419.78	36	559.68

I Would like to transpose it like this:

ID	AE_A	AI_A	AE_B	AI_B	AE_C	AI_C	AE_D	AI_D
35	284	136	5143.03	1450.67	234	110	4692.75	1031.6
43	207	130	4389.86	1419.78	93	36	1890.86	559.68

"if...then do;...end;" statement generates missing values

aw016 — Fri, 05 Mar 2021 22:18:31 GMT

Hello there,

I used "if...then do;...end;" statement to get a new dataset, however, the new created variables are all missing values. I already tried the same format codes to generate another similar dataset with no errors.

Here is my codes:

data my_OR; set OR;
if effect="LDL_10 at gluc_10=8.80" then do group=1; gluc=88; end;
if effect="LDL_10 at gluc_10=9.90" then do group=1; gluc=99; end;
if effect="LDL_10 at gluc_10=12.50" then do group=1; gluc=125; end;
if effect="Gluc_10 at LDL_10=9.05" then do group=2; LDL=90.5; end;
if effect="Gluc_10 at LDL_10=13.51" then do group=2; LDL=135.1; end;
if effect="Gluc_10 at LDL_10=18.75" then do group=2; LDL=187.5; end;
run;

The log looks fine.

NOTE: There were 6 observations read from the data set WORK.OR.
NOTE: The data set WORK.MY_OR has 6 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds

Here is the output:

Thank you so much!

locf CODE CONFIRMATION

jay_bhavsar — Thu, 04 Mar 2021 12:21:49 GMT

Hello all

data new X
input RANDOMNO Visit Value X
DATALINESX
1 0 1
1 1 5
1 2 .
1 3 7
1 4 .
1 5 1
2 0 3
2 1 .
2 2 .
2 3 4
2 4 5
2 5 .
3 0 .
3 1 .
3 2 6
3 3 .
3 4 8
3 5 0
X
RUNX

Proc sort data= new X

By RANDOMNO Visit X

Run X
DATA LOCFX
LENGTH DTYPE $15X
RETAIN retain X
SET new X
BY RANDOMNO Visit X

IF FIRST.RANDOMNO and Visit=0 THEN retain=. X
IF Value NE . and Visit^=0 THEN retain=Value X
IF Value=. THEN DO X
VLOCF=retain X
DTYPE='LOCF' X
END X
ELSE VLOCF=Value X

RUN X

REPLACED SEMICOLE WITH X AS SAS ASK TO REMOVE TO DO THIS BEFORE POSTING

----------------------------------------------------------

Above code I am using to do LOCF. it is giving me a correct result for this data. It may be not correct for large data.

Can you confirm that condition I applied is correct.

is this code will work for a large data also.

please suggest if further refinement to this code should be done.

searching for the next not repetitive date

J_J_J — Wed, 03 Mar 2021 10:43:30 GMT

Hi guys!

Help me please to solve the problem. I try to find score. ID=A gives 1 point, ID=B minus 1 point, but it should be in chronological order, like in attached file. How is it possible to solve it? The result for code 111 should be 1 point.

Thanks in advance.

Flag multiple duplicates (without deleting) so third duplicate has different flag from second

MHines — Mon, 01 Mar 2021 20:37:46 GMT

Hello,

I am analyzing a dataset with multiple duplicate ID numbers in it and am trying to create a flag such that, if an ID appears four times for example, the first appearance is flagged 0, second is flagged 1, third is flagged 2, and fourth is flagged 3. I have approximately 139,000 unique IDs and 356,000 rows total. About 40% of my IDs are unique, but the number of observations per ID ranges from 1 to 98 (I only really care about the first three observations per ID, but I need to be able to differentiate between the second and third). I successfully created a flag that identifies the duplicates, but it does not differentiate between the second and all duplicates following it (it's just a 0 /1 binary indicator currently, where =1 tells me the row is a duplicate). Here is my current code:

data want;

set data have;

by idnumber;

if first.idnumber=0 or last.idnumber=0 then dupflag = 1;

else flag=0;

run;

Any assistance is appreciated. Thank you!

Reading output 2.3 in Fixed Effects Methods for Linear Regression (Paul.Allison)

Phil_NZ — Sun, 28 Feb 2021 20:52:46 GMT

Hi all SAS Experts,

I am new to SAS regression, I am working in finance research. After asking some questions in this forum, some experts ask me to read the book "Fixed Effects Methods for Linear Regression" (Paul Allison, 2005), then search lexjansen for answers then come back to this forum to ask if there are any questions (e.g: and ). I am wondering if the questions about that book are allowed to be posted here, if not, please let me know and I am happy to receive any suggestion to align with the forum's rules.

I am quite a bit confused about that

1. The example in this book (I am standing on chapter 2) is straightforward with simple examples, I am wondering why many suggest me to go through lexjansen to search in any case, can I ask how to use lexjansen website and this book efficiently?

2. Regarding Output 2.2 in this book (page 13), he wrote something that I did not understand, and I do not know how to search from lexjansen website to go through it

I do not understand the part "Output 2.3 shows that although the majority of children did not change in status, about 24% did change in one direction or another", what does "24%" means and where it is on the Table?

Apart from that, the Table output 2.3 also really unfamiliar to me, because I do not know how to read this Table. For example, I do not know pov90 standing there for what, and what do 0 and 1 mean?

Warmest regards.

Add a vertical reference line to time series plot in sas

xliu1 — Fri, 26 Feb 2021 21:41:53 GMT

I try to add a vertical reference line to a time series plot to indicate the year where the forecast starts, but keep getting this error message in sas log. The variable AY has the datetime format Year4. The sas code works for the plot until I add the refline statement. Will appreciate any help with the refline statement! Thanks!

PROC SGPLOT DATA=BACHDEG_Forecast;
BAND Upper=u95 Lower=l95 x=AY / Legendlabel="95% Confidence Limits";
scatter x=AY y=degrees_usf;
series x=AY y=forecast;
refline "AY=2022" / axis=x lineattrs=(thickness=1 color=BLUE pattern=dash);
RUN;

Exclude categorical variable level from chi square analysis, but not from total population

bazingarollcall — Wed, 24 Feb 2021 18:13:28 GMT

Hello,

I have a dataset with several categorical variables. I'd like to produce chi square tests on some of the variables. One of the variables, race, has several levels:

White

Black

Hispanic

Other

Unknown

The level 'unknown' cannot be included in the chi square test, but must be included in the total population of interest, which is ~85,000.

I've tried the following code:

proc format;
   value $plan
    'var1','var2'='var1+2'
    'var3' 'var4' 'var5'='var3-5'
run;

proc freq data=popstudy;
tables (race sex age)*(var1) / chisq;
format var1 $plan.;
where race not= ('Unknown') ;
run;

This reduces the total population to ~60,000, which is not what I need.

I've also tried subsetting the dataset and producing another dataset that removes all values of 'Unknown' race, but this reduces the population as the code above does and is not correct.

My supervisor says I need to rename the 'Unknown' level as equal to 99999 or something similar, and include a "where" statement in my proc freq statement, but I am not sure what they mean by this.

Thank you.

Table selector prompt

Andres_Fuentes — Tue, 23 Feb 2021 21:31:38 GMT

Hello, good afternoon, I would like to ask for a little help with something. In the Guide server we have several tables listed by years, I have a process which analyzes the tables but what I want to achieve is that the user selects the year with a prompt and executes the analysis qwerys.

These are examples with the names of the tables that I have.

table2018

table2019

table2020

thanks for your time.

number of id per date

sasphd — Tue, 23 Feb 2021 18:45:46 GMT

Hello,

I want to calculate the number of id per date or year

data have;
input date id $;
datalines;

1996 5
1996 6
1996 7
1997 1
1998 5
1998 10
1999 11
run;

data want;
input date nbid $;
datalines;

1996 3
1997 1
1998 2
1999 1
run;

Help with PROC SQL syntax error

xliu1 — Mon, 22 Feb 2021 17:20:21 GMT

I keep getting error message from sas log for the following program and will appreciate any help with this. Thanks!

PROC SQL;
CREATE TABLE MIN_ENRL_TERM AS
SELECT H.OASIS_PIDM    as    OASIS_PIDM               
, R.UID              as   USF_ID
,(SELECT  H.REPT_TIME_FRAME as MIN_ENRL_TIME,
        H.OASIS_PIDM  as    MIN_ENRL_PIDM,
        min(CASE WHEN (H.STU_ENTRY_TYP IN ('B', 'E') AND H.STU_RECENT_ADM_TYP IN ('B', 'E') 
                         AND R.CRS_SYSTEM = 'N' AND R.CRS_GROUP = 'A' and R.STU_CLASS_LEVEL ^= 'N' 
                         AND R.TERM_TAKEN >= H.STU_ENTRY_DT) THEN R.TERM_TAKEN
                 WHEN (R.CRS_SYSTEM = 'N' AND R.CRS_GROUP = 'A' and R.STU_CLASS_LEVEL ^= 'N' 
                         AND R.TERM_TAKEN >= H.STU_RECENT_ADM_DT) THEN R.TERM_TAKEN END)) AS FIRST_ENRL_TERM
FROM SID.SID_COURSES_TO_DEGREE  R
JOIN SID.SID_HOURS_TO_DEGREE H ON R.CONTEXT_CODE = H.CONTEXT_CODE AND R.OASIS_PIDM = H.OASIS_PIDM 
                                  AND R.REPT_TIME_FRAME = H.REPT_TIME_FRAME AND R.REPT_TIME_FRAME IN ('201905', '201908', '202001');
QUIT;

display proc freq output

lmyers2 — Fri, 19 Feb 2021 00:54:31 GMT

Hello - I'm trying to get a proc freq output that contains 3 columns (all patients, COPD=0, COPD=1) instead of running 2 procedures and coping the answers over by hand. Can you help?

proc freq order=freq; table race; run;
proc freq order=freq; table race*COPD; run;

Desired output:

All COPD=0 COPD=1

race1

race2

race3

Also, is there a way to order by a certain column or will SAS always order by descending row sum?

in the fixed effects, proc panel with fixeone and proc glm with Absorb have the same regres equation

Boni1 — Wed, 17 Feb 2021 23:14:05 GMT

Read filter from a table sas macro

orangejuss — Wed, 17 Feb 2021 09:07:55 GMT

Is it possible to read code block from a table and create a data by using these code blocks as a filter?

Thanks in advance.

data filter_data;
input table_name $6. filter $30. ;

DATALINES;
Table1 Count>=3 and date="03feb2021"d
Table2 sum>=100 and count<50
;
RUN;

%macro create(table,filter);

proc sql;
create &table. as select
id,

count(distinct x) as x,
sum(y) as y,
count(z) as z
from sample
where &filter. /* %put &filter */
group by id;
quit;

%mend;

data _null_;
set filter_data;
CALL EXECUTE("%create("||table_name||","||filter||");");
run;

PROC APPEND: Variable not appended due to type mismatch

SebastianA — Tue, 16 Feb 2021 22:55:43 GMT

Hi All,

I am new to SAS. I am trying to extract data from SQL Server and load into Teradata.

My source table has few columns which are converted to 'yyyy-mm-dd' format and loaded to Teradata in the PROC sql.

when I am trying to append the dataset to Teradata, it gives me a warning:

WARNING: Variable OPPORTUNITY_TCV not appended because of type mismatch.

From source I am extracting it as below:

FORMAT(CAST([Status Change Date] AS DATETIME),'yyyy-MM-dd') AS [STATUS_CHANGE_DATE],

In the destination DB (Teradata) the column is defined as:

STATUS_CHANGE_DATE DATE FORMAT 'YYYY-MM-DD' DEFAULT DATE '2000-01-01',

while executing the SAS program I am getting the below warning.

WARNING: Variable STATUS_CHANGE_DATE not appended because of type mismatch.

Please suggest how I can resolve the warning.

ID	Y1	Y2	Y3
54	55	79	55
55	83	61	55
56	74	77	60
57	64	88	56
60	87	78	83
61	84	87	77
62	78	68	50
63	89	59	52
64	74	77	60
65	55	79	55

ID	Y1	Y2	Y3
54	55	79	55
55	83	61	55
56	74	77	60
57	64	88	56
60	87	78	83
61	84	87	77
62	78	68	50
63	89	59	52
64	74	77	60
65	55	79	55

ID	Y1	Y2	Y3
54	55	79	55
55	83	61	55
56	74	77	60
57	64	88	56
60	87	78	83
61	84	87	77
62	78	68	50
63	89	59	52
64	74	77	60
65	55	79	55