SAS Data Management topics

Merging rows based on date and location

cbagdon-cox — Tue, 11 Aug 2020 18:33:07 GMT

Trying to merge rows in which an individual was at the same location but has multiple date ranges resulting in multiple rows. Want to have one row per location with the first start date and last end date. Having a blank end date means they are still at that location and that would need to be kept.

I don't even know where to start with this merge. Open to DATA and PROC SQL steps. This data has approximately 500 rows currently for approximately 60 individuals. The reason the data populates like this is because there are separate bed assignments for each row, that level of data is not needed for this output, just location and the dates.

What my data currently looks like:

FULLNAME	START	END	LOCATION
SMITH, JOHN	14Sep2010	15Sep2010	CEDAR
SMITH, JOHN	15Sep2010	28Sep2010	CEDAR
SMITH, JOHN	28Sep2010	27Oct2010	CEDAR
SMITH, JOHN	28Oct2010	20Apr2011	OAK
SMITH, JOHN	20Apr2011	02May2011	OAK
SMITH, JOHN	02May2011	17May2011	OAK
SMITH, JOHN	17May2011	18Jul2011	PINE-A
SMITH, JOHN	18Jul2011	03Oct2011	PINE-A
SMITH, JOHN	03Oct2011	13Dec2011	PINE-A
SMITH, JOHN	13Dec2011	31Jan2012	OAK
SMITH, JOHN	31Jan2012	08Feb2012	OAK
SMITH, JOHN	08Feb2012	08Aug2012	OAK
SMITH, JOHN	08Aug2012	17Dec2012	OAK
MILLER, SALLY	21Aug2015	26Aug2015	PINE-B
MILLER, SALLY	26Aug2015	22Sep2015	CEDAR
MILLER, SALLY	22Sep2015	14Oct2015	CEDAR
MILLER, SALLY	14Oct2015	22Nov2015	CEDAR
MILLER, SALLY	22Nov2015	04Dec2015	CEDAR
MILLER, SALLY	04Dec2015	10Dec2015	CEDAR
MILLER, SALLY	10Dec2015	10Mar2016	OAK
MILLER, SALLY	10Mar2016	29Dec2017	OAK
MILLER, SALLY	29Dec2017	10May2018	OAK
MILLER, SALLY	10May2018	01Nov2018	OAK
MILLER, SALLY	01Nov2018		OAK
JOHNSON, ROBERT	16Apr2017	17Apr2017	MAPLE-A
JOHNSON, ROBERT	17Apr2017	08May2017	MAPLE-A
JOHNSON, ROBERT	08May2017	22May2017	MAPLE-B
JOHNSON, ROBERT	22May2017	31May2017	WILLOW
JOHNSON, ROBERT	31May2017	08Jun2017	WILLOW
JOHNSON, ROBERT	08Jun2017	02Aug2017	WILLOW
JOHNSON, ROBERT	02Aug2017	07Aug2017	MAPLE-C
JOHNSON, ROBERT	07Aug2017	11Sep2017	WILLOW
JOHNSON, ROBERT	11Sep2017	03Apr2018	WILLOW

What I want my data to look like:

FULLNAME	START	END	LOCATION
SMITH, JOHN	14Sep2010	27Oct2010	CEDAR
SMITH, JOHN	28Oct2010	17May2011	OAK
SMITH, JOHN	17May2011	13Dec2011	PINE-A
SMITH, JOHN	13Dec2011	17Dec2012	OAK
MILLER, SALLY	21Aug2015	26Aug2015	PINE-B
MILLER, SALLY	26Aug2015	10Dec2015	CEDAR
MILLER, SALLY	10Dec2015		OAK
JOHNSON, ROBERT	16Apr2017	08May2017	MAPLE-A
JOHNSON, ROBERT	08May2017	22May2017	MAPLE-B
JOHNSON, ROBERT	22May2017	02Aug2017	WILLOW
JOHNSON, ROBERT	02Aug2017	07Aug2017	MAPLE-C

ERROR ##-###: The INDEX function call has too many arguments.

DanielQuay — Thu, 06 Aug 2020 16:03:39 GMT

I am working with data trying to identify opioid useage.

data want;

set have;

IF INDEX(UPCASE(var1), "METHADONE")>0 or INDEX(UPCASE(var2), "METHADONE")>0 or
INDEX(UPCASE(var3), "METHADONE")>0 or INDEX(UPCASE(var4), "METHADONE")>0 or
INDEX(UPCASE(var5), "METHADONE")>0 or INDEX(UPCASE(var6), "METHADONE")>0

THEN OPI_3=1;

run;

This code works for 14 of the 16 substances I am looking for.

But when I am looking for OPANA and CODIENE it is popping up with:

ERROR ##-###: The INDEX function call has too many arguments.

I think this may be something about how the data is formatted but I haven't had to use INDEX before.

How do I clear these errors?

wheddingsjr — Fri, 31 Jul 2020 06:43:35 GMT

Hello all

I have inherited SAS code that has approx. 20 steps and the code had errors throughout. I have been successful in cleaning it up until the last two steps. This is the code for the first one

PROC SQL;
CREATE TABLE WORK.AllClaims1 (DROP= Discharge Date Rename= (Discharge Date1= Discharge Date)) AS 
SELECT *
 ,INPUT(Discharge Date,5.) - 21916 AS Discharge Date1 FORMAT= mmddyy10.
FROM WORK.AllClaims
;QUIT;

This is the error message I get:

22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.

The is the code for the other step

DATA AllClaims_Updated;
SET hphc_EOL_FY20 WORK.AllClaims;
RUN;

This the error message that I get:

ERROR: Variable Age has been defined as both character and numeric.
ERROR: Variable BMT has been defined as both character and numeric.

These are the two columns that are effected and their types.

work.EOL_COHORT Age = Type Numeric, Length 8
BHM = Type Character, Length Character 1

AllCohorts1 Age = Type Character, Length Character 1
BHM = Type Numeric, Length 8

Any assistance with these would be greatly appreciated.

sum one column and group by two others

Keets — Thu, 30 Jul 2020 22:39:53 GMT

Hello,

I have a big dataset with 3 values that are of interest. ID, Visite and WSL_Flaechen.

Visite ranges from 1 to 5 and WSL_Flaechen from 1 to 2.

With the following code i managed to group by Visite and count the WSL_Flaechen for each ID.

But instead i want count of WSL_Flaechen (blue arrow) to be multiplied with the value of WSL_Flaechen (red arrow) and display the sum of that for each ID.

So ID 4001 has

(1*8 + 2*12) = 32 on Visite 1 and

2 + 20 = 22 on Visite 2

and 26 on Visite 3

and so on...

The dataset only has 1s and 2s and the WSL_Flaechen field and i cant seem to just sum them up. I only display the count of 1s and 2s in seperate fields. How to i properly use the sum function here?

When i try to get sum in there i get the "statistical variable other than N without analysis variable" error.

Sincerely,

Keets

/tmp Storage used for joining and sorting

shrikantpwc — Wed, 29 Jul 2020 09:12:06 GMT

Hi Team,

Few of my jobs are getting failed due to insufficient storage, my work file location having sufficient space. /tmp/ getting utilised by job for sorting and joining.

my question is can we change the location of this sorting and joining location permentalty.

Thanks

-shrikant Suvarnkar

Computing the change in values across time and state

r4321 — Thu, 30 Jul 2020 04:45:52 GMT

Updated (7-27-20)

Hello all,

I could use your help figuring out some code. I have different 'buckets' that take on different values depending on the time, bucketcode, and state, and I am trying to compute the change in values (count) across time and state. Please see an illustration of my data below.

A couple new notes:

(1) I need it to accommodate multiple bucketcodes per state per time period.

(2) I need to account for the appearance / disappearance of bucketcodes within a state. For example, a new bucketcode might enter OH at time period 103 that wasnt previously there in the prior time period (102). If a new bucketcode appears in a time period (eg., 102) (that isn't the first) then that should be reflected in the changescore differential for that bucketcode/state/timeperiod triplet with whatever the addition in count was, eg., 1. If a bucket that was there in a time period disappears in a subsequent period (was there in 102, but not in a state-time-bucketcode triplet in the next period, eg., 103) then the state-time-bucketcode triplet should reflect whatever the drop in value is/was for that subsequent period. For example, VA-104-333 has a count of 2, but if VA-105-333 is not there then VA-105-333 would be -2.

(3) Dropping the first time period is fine, but after that if a bucketcode enters a state and time period, that wasn't previously there, it should be treated as entering the dataset.

data have;
input time Bucketcode State $ Count;
datalines; 
101 222 OH 3
102 222 OH 4
103 222 OH 4
103 333 OH 1
104 333 OH 1
101 111 TX 2
102 111 TX 1
103 111 TX 1
104 111 TX 2
101 222 VA 1
102 222 VA 2
103 222 VA 2
104 222 VA 4
104 444 VA 2
;

data want;
input Time Bucketcode State $ Countdiff;
datalines;
102 222 OH 1
103 222 OH 0
103 333 OH 1
104 222 OH -4
104 333 OH 0
102 111 TX -1
103 111 TX 0
104 111 TX 1
102 222 VA 1
103 222 VA 0
104 222 VA 2
104 444 VA 2
;

Thanks!

Selects which branch to use - SAS DATA INTEGRATION

kassialua1 — Wed, 22 Jul 2020 14:25:41 GMT

Hi,

I have branch structures at GIT.

Is there a way to control how SAS DI selects which branch to use? At the moment, all checkins end at our main branch (branch master), instead of the individual resource branch the developer is working on?

best regards

Kassia

How do I import a sas7bdat file to SAS studio and then use it for data manipulation?

jjoy4891 — Mon, 20 Jul 2020 20:48:12 GMT

Hi,

I have 4 sas7bdat files , wanted to concatenate them for data manipulation . How can i import the same to sas university edition and perform concatenation ?

Transpose long to wide with Do Loop

camper — Wed, 15 Jul 2020 16:23:15 GMT

Hello!

I've spent a couple of days researching forums and reading papers and I'm stumped. I don't know how to use arrays, macros, or do loops - and i think that's what's needed for this task. This is more complicated than I can do.

This is fish habitat data. The ORIGIN is the stream where the fish lives and the DESTINATION is another suitable stream. DISTANCE is the stream length between them, and the LEN and VOL are additional attributes. The data must be sorted by ORIGIN, DISTANCE because I want the transformed columns to be in the order of the closest destination habitats.

My dataset as >6000 obs, and some ORIGINs have 25 DESTINATIONS, so there will be about 100 columns in the resulting table. It's what the boss wants - and I'm bummed that I can't figure it out myself.

Here's the data:

data WORK.CLASS(label='Habitat Data');
infile datalines;
input Origin:8. Destination:8. Distance:8. Len:8. Vol:8.;
datalines;
1001 999 11891 26.44 193.00
1005 1016 2422 7.03 8.42
1005 944 19437 16.84 74.17
1010 1012 42986 22.10 262.95
1010 988 48093 9.10 26.36
1012 1010 42986 10.66 472.81
1012 988 44204 9.10 26.36
1012 999 47478 26.44 193.00
1016 1005 2422 30.73 62.22
;;;;

Here's the result I want:

As I move the data into the columns, I have abbreviated their variable names. I highlighted the DISTANCEs so you can see how they dictate the ordering of the columns.

Origin	Dest1	Dist1	Len1	Vol1	Dest2	Dist2	Len2	Vol2	Dest3	Dist3	Len3	Vol3
1001	999	11891	26.44	193.00
1005	1016	2422	7.03	8.42	944	19437	16.84	74.17
1010	1012	42986	22.10	262.95	988	48093	9.10	26.36
1012	1010	42986	10.66	472.81	988	44204	9.10	26.36	999	47478	26.44	193.00
1016	1005	2422	30.73	62.22

Thanks a lot! This has been fun, but a dead end. Hope someone out there has fun with it! I'm using SAS 9.4.

Compute the number of observations and sum in a moving window

Thomas_mp — Fri, 10 Jul 2020 20:19:20 GMT

Export/Print bar graph along with table in HTML format using SAS Data integration

AkshayS — Wed, 08 Jul 2020 09:00:37 GMT

Hi All,

I am trying to print both table and bar graph in HTML but able to only get table in output using SAS DI.

Kindly assist me what modification is needed in my below code:

ods listing close;
ods html path='/ACTDATA/fireuw/' (url=none)
body='sgplot.html';
goptions reset=all;

PROC SQL;
create table CARS1 as
SELECT make, model, type, invoice, horsepower, length, weight
FROM
SASHELP.CARS
WHERE make in ('Audi','BMW')
;
RUN;
proc print;
quit;

proc SGPLOT data = cars1;
vbar length /group = type GROUPDISPLAY = CLUSTER;
title 'Cluster of Cars by Types';
run;
quit;

ods html close;
ods listing;

Thanks,

Akshay

Column Header Validation

Flappyduck — Tue, 07 Jul 2020 13:46:26 GMT

Hi,

I'm looking for some help on creating some form of custom transformation to check the column headers a .csv file has when it get read into DI Studio.

I have a list of headers which should be in the table and when the file gets read in if it doesn't contain the correct headers, i want it will get moved to a check file so that the job doesn't fail. Any suggestions on the best way to set this up?

Thanks

Rearranging data in SAS 9.4

Thomas_mp — Sun, 05 Jul 2020 18:47:01 GMT

Good morning,

I need some help rearranging data in a very large data set using SAS 9.4.

I have the data as follows (please see also attached Excel file) :

data k;

input date va vb vc vd ;

cards ;

20000630 6.1207 8.7044 5.3283 5.846

20000929 6.1158 8.6934 5.3253 5.8423

20001229 5.7898 7.9828 5.1254 5.5832

20010330 4.9815 7.5266 4.4934 4.5082

;

run;

I would like to have the data as:

va	20000630	6.1207
va	20000929	6.1158
va	20001229	5.7898
va	20010330	4.9815
vb	20000630	8.7044
vb	20000929	8.6934
vb	20001229	7.9828
vb	20010330	7.5266
vc	20000630	5.3283
vc	20000929	5.3253
vc	20001229	5.1254
vc	20010330	4.4934
vd	20000630	5.846
vd	20000929	5.8423
vd	20001229	5.5832
vd	20010330	4.5082

Thank you for your help,

Tomas

Quick datepart/date format query.

MRDM — Fri, 03 Jul 2020 14:02:35 GMT

Hi, This might be fairly obvious but formats have always been my downfall and I'm up against the clock or I'd try experiment. I'm doing this in an expression on an Extract Transformation in DI Studio.

CASE WHEN DOB  ^= .
THEN input(cats(year(datepart(DOB)),month(datepart(DOB))),6.)
ELSE . END

Gives me 20055, I'd like 200505 (dates like 200512 are already fine). The output is a straight numeric rather than a date so I didn't think it would be an issue, from a quick google I found using z as the format so tried z6. but that didn't work.

Any ideas? Thanks

Move dataset from one library to other

David_Billa — Mon, 29 Jun 2020 18:57:00 GMT

Is there a way that we can move the dataset from one library to other library?
In one of my DI job, I want to change the library of the source dataset. Any help?

Convert an excel data table into SAS format

saraphdnz — Sat, 27 Jun 2020 02:54:56 GMT

Hello there,

I have an excel data set in the following format (sample data attached) for 5000 firms with 324 months of data.

I need the following structure of rows and columns:

ID, Date, Y, X.

it is assumed that the data is sorted or can be sorted by ID and DATE, such that within each ID block, the observations are in DATE order.

I have created a dataset in excel manually (attached as DSET1) to match with this structure and able to import and run the macro on the sample but I am unable to find anything that can convert this big data structure as required in SAS.

Do I need to look in Excel or I can do it in SAS as well? I would appreciate it if I can get some guidance on this.

Regards,

Sara

To identify the continuity and loyalty of customer over the years and find maximum continuity year

AkshayS — Fri, 26 Jun 2020 16:08:05 GMT

Hello Everyone,

I am looking to create a sequence variable based on two columns : Cust_name and Year.
There are different type of customers as below:
Customer1 was with me from 2001 to 2003
Customer2 was with me only for 2001 and 2002
Customer3 was only for one year 2001
Customer4 was with me alternate year 2003,2005,2007
Customer5 2001,2002,2005,2006,2007

I want to find maximum number of years I was able to provide service to my customers.
So, Output I am looking is:
Customer1 output as 3 (as he was 2001,2002,2003)
Customer2 as 2 (as he was 2001,2002)
Customer3 as 1 (as he was only for yr 2001)
Customer4 as 1 (alternate yr so no continuous year)
Customer5 as 3 ( he was there continuously 2(2001,2002) and 3(2005,2006,2007) so I will choose max as 3).

PFB dataset for your reference:

data abc;
input customer_id Customer_name $ 3 - 11 transaction_date :mmddyy10. ;
format transaction_date mmddyy10.;
datalines;
1 Customer1 03/31/2001
1 Customer1 03/31/2002
1 Customer1 03/31/2003
2 Customer2 03/31/2001
2 Customer2 03/31/2002
3 Customer3 03/31/2001
4 Customer4 03/31/2003
4 Customer4 03/31/2005
4 Customer4 03/31/2007
5 Customer5 03/31/2001
5 Customer5 03/31/2002
5 Customer5 03/31/2005
5 Customer5 03/31/2006
5 Customer5 03/31/2007
;
run;

proc print data=abc;
run;

Cust_Name	Year	Output1_Sequence	Final_output(max(Output1 by Cust_name))
1	2001	1	3
1	2002	2	3
1	2003	3	3
2	2001	1	2
2	2002	2	2
3	2001	1	1
4	2003	1	1
4	2005	1	1
4	2007	1	1
5	2001	1	3
5	2002	2	3
5	2005	1	3
5	2006	2	3
5	2007	3	3

Looking forward for the insights.
Thanks in advance!

Error in "where clause" for date in PROC REG

saraphdnz — Fri, 26 Jun 2020 01:23:49 GMT

Hello there,

I am facing an error issue while running the PROC REG. My dataset(attached) named DSET1 have the following structure of rows and columns: ID, DATE Y and X with the format:

I have inserted the code and the full log here. I need help to run the regression for my dataset that contains data from Jan1990 - Dec2019 for each ID.

/** Import an XLSX file.  **/

PROC IMPORT DATAFILE="H:\DSET1.xlsx\"
		    OUT=WORK.dset1
		    DBMS=XLSX
		    REPLACE;
RUN;

/** Print the results. **/

PROC PRINT DATA=WORK.dset1; RUN;


/** SortedIn ID & Date order**/
proc sort data=WORK.dset1 out=temp;
by id date;
run;


/** list of variables and attributes **/

Proc Contents data=work.temp;
run;

/** Estimate coefficients for each ID **/

proc reg noprint data=temp outest=regout1 edf ;
	where Date between ‘01JAN2020’d and ‘31DEC1989’d;
 	model y = x;
 	by id;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 /** Import an XLSX file. **/
74
75 PROC IMPORT DATAFILE="H:\Paper 3 SAS - Single Share Class\SAS MAcro help docs\DSET1.xlsx\"
76 OUT=WORK.dset1
77 DBMS=XLSX
78 REPLACE;
79 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 1800 observations and 4 variables.
NOTE: WORK.DSET1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.11 seconds
cpu time 0.06 seconds

80
81 /** Print the results. **/
82
83 PROC PRINT DATA=WORK.dset1; RUN;

NOTE: There were 1800 observations read from the data set WORK.DSET1.
NOTE: PROCEDURE PRINT used (Total process time):
real time 2.22 seconds
cpu time 2.18 seconds

84
85 /** SortedIn ID & Date order**/
86 proc sort data=WORK.dset1 out=temp;
87 by id date;
88 run;

NOTE: There were 1800 observations read from the data set WORK.DSET1.
NOTE: The data set WORK.TEMP has 1800 observations and 4 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

89
90 /** Estimate coefficients for each ID **/
91
92 proc reg noprint data=temp outest=regout1 edf ;
93 where Date between ‘01JAN2020’d and ‘31DEC1989’d;
_
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,
a numeric constant, a datetime constant, a missing value, (, +, -, INPUT, NOT, PUT, ^,
~.
ERROR 76-322: Syntax error, statement will be ignored.
94 model y = x;
95 by id;
96 run;

WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
NOTE: PROCEDURE REG used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.REGOUT1 may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.REGOUT1 was not replaced because this step was stopped.
97
98 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
111

Regards,

Sara

Conditionally execute precode and postcode in SAS DI studio

David_Billa — Sat, 20 Jun 2020 17:55:17 GMT

I've the ETL Jobs in DI Studio where it has precode and postcode as below. Main purpose of this code to get the Job Status and write in the SQL table.However I don't want this precode and postcode to execute when any of the ETL Job needs an update or enhancement. I don't wish to remove the precode and postcode either.

SAS program for the macro %Job_status is called in autoexec file.

I'm looking for an way to control the precode and postcode macro execution by macro variable. E.g.

%let run_macro_pre_post_code=Y; when I need to run precode and postcode and when %let run_macro_pre_post_code=N; then precode and postcode should not run.

/*Precode*/
%job_status(_status=STARTED,                                      
                _jobName=&etls_jobName. 
               );  

/*Postcode*/
%job_status(_status=FINISHED,                                     
                _jobName=&etls_jobName., 
                _job_return_code=&syscc.
   );

I would be thankful if someone of you help me with the logic or the program to accomplish it.

SAS/ACCESS interface to Teradata cannot be loaded

knn1888 — Thu, 18 Jun 2020 23:32:35 GMT

Hi all,

I'm trying to access Teradata through sas and got this error:

ERROR: The SAS/ACCESS Interface to Teradata cannot be loaded. The SASTRA code appendage could not be loaded.

ERROR: A Connection to the teradata DBMS is not currently supported, or is not installed at your site.

performed

proc product_status;

run;

output:

For SAS/ACCESS Interface to Teradata ...

Custom version information: 9.4_M1

and

proc setinit;

output:

---SAS/ACCESS Interface to Teradata 14JUL2020

I also check the binaries file .dll and I have both of them. Why do I still have this error then?

Connecting to Databases using Data Flux ODBC Drivers

Rama_V — Wed, 10 Jun 2020 23:54:55 GMT

I am looking for advice how to connect to databases from Data Flux management Studio/Server using Data Flux ODBC Drivers. I could connect using drivers provided by applications like SQL Server or Teradata driver. I am trying to figure out what steps need to be taken to use Data Flux ODBC drivers.

I have followed below documentation link, as per step 5 seems like I need to update on server side which about to connect using the driver.
https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.6/dmpdmsug/Content/dfU_T_DataConnODBC.html

For test, I am using SQLSERVER Express. but I had connectivity issues.

Could any one please guide me what steps to be taken at least for SQL Server connectivity?

Thanks,

Rama

Transpose in DI Studio

crawfe — Wed, 10 Jun 2020 12:54:59 GMT

Hi, I am trying to transpose daily data using UserWrittenCode in DI Studio

which I will append each day to a master table.

HAVE (data):

DATE STATUS COUNT_1

06/01/2020 Done 4

06/01/2020 Start 2

06/01/2020 IP 1

06/01/2020 FAIL 7

WANT (test1):

Date Done Start IP FAIL

06/01/2020 4 2 1 7

Can you see an error in this code? Thanks!

%let etls_tableExists = %eval(%sysfunc(exist(&_OUTPUT, DATA)));
%if (&etls_tableExists) %then %do;
proc sql noprint;
drop table &_OUTPUT.;
quit;
%end;

PROC TRANSPOSE data=&_INPUT out=test1;
by DATE;
id STATUS;
var COUNT_1;
RUN;

DATA &_OUTPUT. ;
SET test1;
run;

Sas Viya Data Explorer

kcoley — Sun, 07 Jun 2020 16:47:50 GMT

Does anyone no what server configuration files sas viya data explorer use? Apparently it does not read the same files as studio. Data explorer is unable to find my ODBC drivers while studio connects. It is a new install and im running a linux environment

How can I condense variables into one row?

Ross123123 — Fri, 05 Jun 2020 22:48:54 GMT

Here is what I have to do:

Using the “schools1” data, create a new dataset called "schools2" which includes
average and median sat scores for sat1, sat2, sat3, and overall satavg with school as the
level of analysis. If you do this correctly, there should be 30 observations and 11
variables (including 8 different SAT variables) in your resulting dataset (use PROC
MEANS to do this).

So I think I have to basically get rid of the student variable and just have one row per school, but im really not sure how to do that using proc means, I've been trying for a couple hours but don't even really know where to start.

I added some of the code to make the data and a picture of the schools1 dataset.

Data is both character and numeric for large number of variables

Betsy — Fri, 05 Jun 2020 20:35:02 GMT

Hi all, it's my first time posting here.

I have 8 datasets each with 800+ variables that I am trying to stack. I am getting "ERROR: Variable X has been defined as both character and numeric."

I know that this error comes from some of the datasets defining this variable as character and some as numeric.

However, with such a large number of variables (the error occurs on at least 400 of them) it's impractical to switch each variable using put/input.

I am wondering if anyone has a solution that fixes a large number of these errors relatively quickly.

Thank you!!

Unable to understand the SAS code node working inside a process flow

ShikhaAgarwal — Fri, 05 Jun 2020 02:18:15 GMT

Hi All,

I am unable to access the worktables which has my data for profiling through SAS code node.

I have added that table through setting of source bidding.

So my data step contains a file reader and work table writer.

i reference this in process flow as i need to execute some SAS codes.

But SAS code sas the work table doesnt exits

I cant see the work table as active tab while filling the source binding details in input.

Where can i see my SAS libraries in dataflux?

Do loop

Mathis1 — Fri, 29 May 2020 13:42:01 GMT

Hello,

I'm trying to generate a variable (named "NEW") that is set to i=1 when first.VAR is observed and then is incremented by one until last.VAR.

But i'd like that each time a new first.VAR is met, NEW is set to i+1.

And as a last requirement, i'd like that when NEW=12, then NEW+1 is set back to 1.

I tried something like this (In the attached table VAR is named PUIS_CLASS) :

data WAANT;
Set HAVE;
Retain NEW;
	Do i=1 to 12;
	By PUIS_CLASS;
	if first.PUIS_CLASS then NEW=i;
	else NEW=i+1;
	if last.PUIS_CLASS then output;
	end;
run;

But it's not the solution...

Thank you again for your help 🙂

Looking to reorganize data around time based events

r4321 — Fri, 29 May 2020 02:52:21 GMT

Hello all,

I have data on companies observed each year 2000-2015 (16 years) where I am looking to reorganize it based on a variable. This variable is an event-based variable that takes on values from 0-1 (1 if it happened). If (and/or when) the variable takes on a 1, I would like to collapse the rows and sum the values on other relevant variables. I would also like to create different indicators based on this: (1) one variable indicates when time starts (2) one variable that indicates when the time ends, and (3) a variable that indicates 0/1 whether an event took place at all. Essentially, each observation of a case becomes a start-stop as to when the event-based variable was observed.

I Illustrate below what I have and then want.

Have:

Time	ID	County	FocalEvent	X1mva	X2police
1	222	ABC	1	10	15
2	222	ABC	0	5	10
3	222	ABC	0	15	5
4	222	ABC	0	2	4
5	222	ABC	1	5	10
6	222	ABC	0	10	5
7	222	ABC	0	5	10
8	222	ABC	0	4	4
9	222	ABC	0	5	5
10	222	ABC	1	10	10
1	333	BBB	0	3	4
2	333	BBB	0	2	1
3	333	BBB	0	5	5
4	333	BBB	1	10	5
5	333	BBB	0	15	15
6	333	BBB	0	10	10
7	333	BBB	0	10	10
8	333	BBB	1	5	5
9	333	BBB	0	10	10
10	333	BBB	0	15	15
1	444	CCC	0	1	1
2	444	CCC	0	5	5
3	444	CCC	0	4	4
4	444	CCC	0	5	5
5	444	CCC	0	1	1
6	444	CCC	1	5	5
7	444	CCC	0	10	10
8	444	CCC	0	5	5
9	444	CCC	0	5	5
10	444	CCC	0	5	5
1	555	DDD	0	1	1
2	555	DDD	0	1	1
3	555	DDD	0	1	1

Want:

ID	County	X1mva	X2police	Time1	Time2	Event
222	ABC	10	15	1	1	1
222	ABC	27	34	1	5	1
222	ABC	34	34	5	10	1
333	BBB	20	15	1	4	1
333	BBB	40	40	4	8	1
333	BBB	25	25	8	10	0
444	CCC	21	21	1	6	1
444	CCC	25	25	6	10	0
555	DDD	3	3	1	3	0

Thanks for your help.

Average Difference between two date columns

AshJuri — Thu, 28 May 2020 21:39:26 GMT

Hi everyone,

I am a first time user of sas enterprise guide and I am trying to complete a simple calculation using this system. I have two date columns called "start date" and "end date". The formats of the date columns are in, for example, "08JUN2019". I calculated the difference between the two columns in order to produce the column "difference" which gives me the number of days that have elapsed. How would I calculate the average of the subtraction between "end date" and "start date" in sas enterprise guide?

Thank you in advance,

Converting R to SAS Code.

sandyzman1 — Thu, 28 May 2020 17:24:33 GMT

Hi All,

I am trying to run correlation analysis in SAS that was previously done in R. I need to subset the dataset before the analysis, and I am having trouble understanding the R code for subsetting the dataset. Could anyone please help me to interpret the code and suggest equivalent code in SAS?

I have a knowledge of Base SAS and very little knowledge of R. Also, I am not very good at DO loop.

I need to reproduce the following result from R to SAS:

for(i in unique(data$Site)){
sum1_alternate <- sum1 <- sum2 <- sum3_alternate <- 0
for(j in unique(data[data$Site==i,]$id)){
if(unique(data[data$site==i & data$id==j,]$ind)==1){
ind1_alternate <- ifelse(j %in% temp$id, 1, 0)
sum1_alternate<- sum1_alternate + ind1_alternate

if(ind1_alternate == 1){
ind1 <- ifelse(min(temp[temp$id==j,]$age)<21, 1, 0)
sum1 <- sum1+ind1

ind2 <- ifelse(min(temp[temp$id==j,]$age)>21, 1, 0)
sum2 <- sum2+ind2
}
}
i

f(unique(data[data$site==i & data$id==j,]$ind)==0){
sum3_alternate <- sum3_alternate+1
#sum_info <- summary(data[data$site==i & data$id==j,]$age)
}
}

sum1_alternate_site <- c(sum1_alternate_site, sum1_alternate)
sum1_site <- c(sum1_site, sum1)
sum2_site <- c(sum2_site, sum2)
sum3_alternate_site <- c(sum3_alternate_site, sum3_alternate)
#sum3_site <- c(sum3_site, sum3)
}

percent1 <- sum1_alternate_site/(sum1_alternate_site+sum3_alternate_site)
percent2 <- sum1_site/(sum1_site+sum2_site+sum3_alternate_site)

plot(site_mean$x, percent1, xlab="Mean Age", ylab="Percentage of surgery", main="mean age vs percentage of surgery")

abline(lm(percent1 ~ site_mean$x))

cor.test(percent1, site_mean$x)

plot(site_mean$x, percent2, xlab="Mean Age", ylab="Percentage of surgery", main="mean age vs percentage of surgery <21 year-old")

abline(lm(percent2 ~ site_mean$x))
cor.test(percent2, site_mean$x)

Thanks.

Concatenate values by other values

Mathis1 — Thu, 28 May 2020 15:30:24 GMT

Hello,

Please find attached the table that will illustrate my problem. I'd like that for each value of my variable PUIS_CLASS, I have the variable NEW which is the concatenation of all the values of GROUPEACT occuring when the PUIS_CLASS value is observed.

For instance, the two first lines would be :

PUISS_CLASS GROUPEACT

1_K "20_24_25_26_27_28"

2_L "24_25_27"

etc...

Thank you for your help 😄

How to practice SAS Data Integration Studio online (SAS Academy courses)?

andreazanetti — Thu, 28 May 2020 05:57:47 GMT

Hi, very basic question. I am following the SAS Academy courses for Data Science.

In the Data Curation part, SAS Data Integration Studio is used.

I was wondering whether there is a possibility to practice online.

For example, in the Coursera SAS course one cat get access to the Viya platform and practice there.

Is there anything of the kind for the SAS Academy courses?

Thanks

Looking to create indicator variables that help me organize a matched sample

r4321 — Thu, 04 Jun 2020 04:26:32 GMT

Hello all,

Updating the description of the code I am hoping to get help with:

I have a dataset where I am hoping to indicator variables that help me better organize a matched sample from my data. I have data on counties observed each week over the course of a year, some of them experienced a focal event (i.e., if P =1) and some did not and will serve as controls. Additionally, some counties experienced the focal event more than once. I want to add an indicator variable that identifies before and after these focal events at different durations for both the treatments and the non-treated counties. I need at 1 week, 2 weeks, 3 weeks, 4 weeks, and 5 weeks, but only illustrated up to 2 weeks in the example below.

In addition, would be ideal if once a county experienced a focal event they can no longer be in the control set from there forward, but it’s okay if they stay in the dataset and experience a focal event again.

I believe I need some variables such as an eventweek variable that identifies the week of the focal event, a before/after variable (0/1 if before after the focal event), a duration variable that identifies the number of weeks before and after (i.e., at 1, 2, 3, 4, and 5), and a variable (treated) that indicates that a county has been treated (after it has been treated since a county should be able to stay in the pool of controls to serve as a control until it gets treated if at all).

Essentially, I think I need to duplicate sets of observations to create windows of treated and non-treated counties around the focal events I am interested in examining. I also like to add a few other variables: (1) a variable that indicates which treatment number this is for a county (i.e., first treatment, second treatment, …) (2) a variable that indicates whether a county was treated within a short time window of a previous time it was treated (=1 if treated within <=5 time periods, 0 otherwise) and (3) a variable that indicates the degree of treatment (I want focal event variable to be binary in the wantdata but in the have data it is a actually >1 sometimes since it is a count variable), so if focal event was 2, for example, this variable would = 2. See below for example.

Also, I should note that is a focal occurred before week 6, the durations for those observations may not be able to get up to the full 5 weeks before and after. For example, if a focal event occurred in a county in week 2, I'll only have the 1 week duration for that focal event.

Thanks!

Have:

Time	ID	County	FocalEvent
1	222	ABC	0
2	222	ABC	0
3	222	ABC	0
4	222	ABC	0
5	222	ABC	1
6	222	ABC	0
7	222	ABC	0
8	222	ABC	0
9	222	ABC	0
10	222	ABC	0
11	222	ABC	0
12	222	ABC	0
13	222	ABC	0
1	333	BBB	0
2	333	BBB	0
3	333	BBB	0
4	333	BBB	0
5	333	BBB	0
6	333	BBB	0
7	333	BBB	0
8	333	BBB	0
9	333	BBB	0
10	333	BBB	0
11	333	BBB	0
12	333	BBB	0
13	333	BBB	0
1	444	CCC	0
2	444	CCC	0
3	444	CCC	0
4	444	CCC	0
5	444	CCC	1
6	444	CCC	0
7	444	CCC	0
8	444	CCC	0
9	444	CCC	0
10	444	CCC	1
11	444	CCC	0
12	444	CCC	0
13	444	CCC	0
1	555	DDD	0
2	555	DDD	0
3	555	DDD	0
4	555	DDD	0
5	555	DDD	0
6	555	DDD	0
7	555	DDD	0

Want:

Time	ID	County	FocalEvent	EventWeek	Duration	BeforeAfter	Treated	Tnumber	Trecent	Tdegree
4	222	ABC	0	5	1	0	1	1	0	0
5	222	ABC	1	5	1	1	1	1	0	0
4	333	BBB	0	5	1	0	0	0	0	0
5	333	BBB	0	5	1	1	0	0	0	0
4	444	CCC	0	5	1	0	1	1	0	0
5	444	CCC	1	5	1	1	1	1	0	0
4	555	DDD	0	5	1	0	0	0	0	0
5	555	DDD	0	5	1	1	0	0	0	0
9	444	CCC	0	10	1	0	1	2	0	0
10	444	CCC	1	10	1	1	1	2	0	0
9	333	BBB	0	10	1	0	0	0	0	0
10	333	BBB	0	10	1	1	0	0	0	0
3	222	ABC	0	5	2	0	1	1	0	0
4	222	ABC	0	5	2	0	1	1	0	0
5	222	ABC	1	5	2	1	1	1	0	0
6	222	ABC	0	5	2	1	1	1	0	0
3	333	BBB	0	5	2	0	0	0	0	0
4	333	BBB	0	5	2	0	0	0	0	0
5	333	BBB	0	5	2	1	0	0	0	0
6	333	BBB	0	5	2	1	0	0	0	0
3	444	CCC	0	5	2	0	1	1	0	0
4	444	CCC	0	5	2	0	1	1	0	0
5	444	CCC	1	5	2	1	1	1	0	0
6	444	CCC	0	5	2	1	1	1	0	0
3	555	DDD	0	5	2	0	0	0	0	0
4	555	DDD	0	5	2	0	0	0	0	0
5	555	DDD	0	5	2	1	0	0	0	0
6	555	DDD	0	5	2	1	0	0	0	0
8	444	CCC	0	10	1	0	1	2	0	0
9	444	CCC	0	10	1	0	1	2	0	0
10	444	CCC	1	10	1	1	1	2	0	0
11	444	CCC	1	10	1	1	1	2	0	0
8	333	BBB	0	10	1	0	0	0	0	0
9	333	BBB	0	10	1	0	0	0	0	0
10	333	BBB	0	10	1	1	0	0	0	0
11	333	BBB	0	10	1	1	0	0	0	0

SAS Data Integration Studio will always return &JOB_RC = 0 even where there is Warning and Error

WorkingMan — Wed, 27 May 2020 02:19:00 GMT

I am using Status Handling tab in SAS DI Studio to handle different for Job with Success, Warning, and Error status.

I will then pass this status to a autocall macro script to update to a table accordingly. By right, when job success (JOB_RC = 0), it will pass in the value of success to my macro script via autocall. I did the same for warning and error. I also put

%print Job_rc is &JOB_RC;

in the status handling to see what is the job_rc as every job_rc indicates different job status.

I tried with success run, run with warning, and error, all returned &job_rc = 0.

Now I am confused. Why is this happening? SAS DI Studio first assign job_rc =0 as standard in the job but there is no assignment again when the job ends? I am sure there must be a way for assigning the right job_rc value so that we can make use of this value under "Status Handling".

Import asc file question: Why does it work with a file reference, but not with (the same!) path?

AnnaBaukje — Fri, 22 May 2020 10:07:23 GMT

Hi!

I'm having trouble to understand why this does work:

FILENAME asc0808 FILESRVC

FOLDERPATH='/07. Team/02.Development/Case1'

FILENAME='2019.08.08_mutations.asc';

data a1;

infile asc0808 delimiter = '|' dsd lrecl=1000 firstobs=1;

informat onbewerkt $1000.;

format onbewerkt $1000.;

input onbewerkt;

run;

And this does not

data a1;

infile '/07. Team/02.Development/Case1/2019.08.08_mutations.asc' delimiter = '|' dsd lrecl=1000 firstobs=1;

informat onbewerkt $1000.;

format onbewerkt $1000.;

input onbewerkt;

run;

The error message is: Physical file does not exist, '/07. Team/02.Development/Case1/2019.08.08_mutations.asc'

This is the exact location I see when I look at the properties of the file reference of asc0808

Can anybody explain? To me I'm doing exactly the same, but apparently not...

Thanks!

SAS EG Data Export Fails

CurtisSmithDCAA — Thu, 21 May 2020 14:58:54 GMT

A co-worker is having a new problem with SAS EG. After successfully importing an Excel file, when using the Export tab in hopes of saving the data to an external SAS data set, the SAS EG interface does nothing. The usual Save As dialog box never opens. The Windows blue circle icon spins momentarily and then nothing happens.

Any ideas?

identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 13:59:05 GMT

Hello Everyone,

I have a dataset for multiple records/ patient. Each record indicates a separate clinic visit for a certain patient identified by a unique id.

patient. unique id

patient 1- abcd

patient 2- efgh

patient 3. ijkl

the data looks as follows

unique id clinic visit. Aspirin statin

abcd 1 1

abcd 2 1

abcd. 3

abcd 4 1

efgh 1 1

efgh. 2

efgh. 3 1

ijkl 1 1. 1

ijkl 2.

ijkl 3 1

ijkl. 4

So looking at this, patients were either prescribed aspirin and statin on the same day (patient ijkl) or were only prescribe statin (patient efgh) or were prescribed both aspirin and statin but at different visits (patient abcd). My goal is to identify patients who have been prescribed aspirin and statin regardless of the visit. For clarification, I would like to identify patients abcd and ijkl in this instance. My understanding is that if I use the "if aspirin=1 AND statin=1 then var1=1..." command it will look across each record individually and not consider that those records belong to a single patient.

Happy to provide more details

appreciate your help

Can write out ó or ø but it's read in as Ã³ or Ã¸

MRDM — Wed, 20 May 2020 17:08:20 GMT

Hi,

I'm writing out a delimited file and one of the fields contains the value ó, however when I read the file back in it's being read in as Ã³, I've also tried ø which comes in as Ã¸ any ideas? Simplified code:

data &_OUTPUT.;
	length UNIQUE_REFERENCE $250.;
infile "&filename." dlm=',' dsd eov=eov truncover firstobs=2;
input UNIQUE_REFERENCE $;
eov=0;
run;

Data is basically

ID56453óHELPME

It's reading everything in correctly except that character.
Essentially my issue is that I need to concatenate two ID fields into the UNIQUE_REFERENCE field, it's then sent off to another system (with loads of other fields) that does some stuff then sends me it back, I then split my UNIQUE_REFERENCE back into my two ID fields. The lengths can vary so I can't use a scan or anything like that, the other system also strips out any special characters which limits my option for a delimiter. I also can't use anything common that might appear in a user entered ID. That leaves me with limited options, ó and ø is an example of a character that it can pass back to me, unfortunately it seems SAS can happily write that out to a file, but reading it in it is converting it to Ã³ and Ã¸ using the code above, any ideas? Thanks

Convert a multiple length character price variable to numeric

Mathis1 — Wed, 20 May 2020 09:19:26 GMT

Hello,

I have a character variable under this form :

61.39€

47.32€

124.45€

12.3€

and i'd like to convert it into a numeric variable under that form :

61.39

47.32

124.45

12.3

I tried this code :

Data WANT;
Set HAVE;
NEW=input(strip(scan(VAR,-1,"€")), 5);
run;

but i don't get what i want. I only get the price with three digits before the comma. Moreover those prices are only displayed with one decimal instead of 2.

thank you for your help 🙂

How to create a variable based on another variables per ID

lizwarr — Wed, 20 May 2020 06:17:18 GMT

I have a data set containing ID, Visit Type, and Date.

ID	Visit	Date
1	clinic	20060505
1	hospital	20060506
1	hospital	20061217
2	clinic	20060301
2	clinic	20060305
2	clinic	20070503
2	clinic	20070506
2	clinic	20100505
3	clinic	20061112
4	clinic	20080103
4	clinic	20081012
4	hospital	20081227
5	clinic	20050325
5	hospital	20050412
5	hospital	20070510
5	clinic	20061010
5	clinic	20061231
5	clinic	20070125
6	clinic	20060718
6	clinic	20060817
6	clinic	20070918

Now based on this, I want to define "Index Date."

For patients who visited both hospital and clinic, I want the index date to be the first visit of hospital.

For patients who only visited clinics, I want the index date to be the first visit of clinic.

So the new table I want would look something like this

ID	Visit	Date	Index Date
1	clinic	20060505	20060506
1	hospital	20060506	20060506
1	hospital	20061217	20060506
2	clinic	20060301	20060301
2	clinic	20060305	20060301
2	clinic	20070503	20060301
2	clinic	20070506	20060301
2	clinic	20100505	20060301
3	clinic	20061112	20061112
4	clinic	20080103	20081227
4	clinic	20081012	20081227
4	hospital	20081227	20081227
5	clinic	20050325	20050412
5	hospital	20050412	20050412
5	hospital	20070510	20050412
5	clinic	20061010	20050412
5	clinic	20061231	20050412
5	clinic	20070125	20050412
6	clinic	20060718	20060718
6	clinic	20060817	20060718
6	clinic	20070918	20060718

I tried using DO loop but seems like I just can't get it right.

How to solve this?!

Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux?

ShikhaAgarwal — Mon, 18 May 2020 08:12:55 GMT

So the requirement is not just profiling and exploring the data but to be able to highlight the incorrect values by referencing a metadata table which has pattern , datatype, length everything mentioned for each field.

I could find any way to do it in dataflux other than writing my own SAS codes.

Let me know the feasibility please'.

How do I configure SCD Type 2 Loader to have VALID END = Next Valid Start -1 Second?

WorkingMan — Thu, 14 May 2020 15:55:08 GMT

I am using SAS Data Integration Studio and for my jobs with SCD Type 2 loader, I want my VALID END DTTM to be 1 sec before the next VALID START DTTM.

Example as below:

ID Name VALID_START_DTTM VALID_END_DTTM

1 BEN 01JAN2011:00:00:00 01JAN5999:00:00:00

Ben changed his name to Ali. Therefore, we will expire his name and append a new record as below (This is what I want to achieve)

ID Name VALID_START_DTTM VALID_END_DTTM

1 BEN 01JAN2011:00:00:00 31JAN2011:23:59:59

1 ALI 01FEB2011:00:00:00 01JAN5999:00:00:00

As we can see, VALID_START_DTTM of ALI is just 1 sec after BEN's VALID_END_DTTM. This is what i want to achieve.

The default SCD Type 2 Loader in SAS DI Studio will set VALID_START_DTTM of ALI and BEN's VALID_END_DTTM to be at the same exact datetime when the previous/existing record expires.

Example for the existing type 2 loader(that i do not want)

ID Name VALID_START_DTTM VALID_END_DTTM

1 BEN 01JAN2011:00:00:00 01FEB2011:00:00:00

1 ALI 01FEB2011:00:00:00 01JAN5999:00:00:00

Is there anyway to do so?

Import CSV : Make Slash information as a new lilne

Mathis1 — Thu, 14 May 2020 09:34:02 GMT

Hello,

I have a csv file such as some rows are like this (always on the second columns) :

6088

06000/06100/06200/06300

BLABLA

BLIBLI

BLOBLO

And i'd like that when I import it on SAS, I obtain (for this piece of information) :

6088	06000	BLABLA	BLIBLI	BLOBLO
6088	06100	BLABLA	BLIBLI	BLOBLO
6088	06200	BLABLA	BLIBLI	BLOBLO
6088	06300	BLABLA	BLIBLI	BLOBLO

Any idea ?

🙂

read CSV with infile and with comma as decimal point

marieK — Mon, 11 May 2020 14:29:16 GMT

Hi everybody,

I would like to read a CSV file into SAS (SAS 9.4) which has commas as decimal points. The number of decimal places are different. Here is an example of my csv file:

ID	pop
5119	77,09
5120	74,52
5122	89,54
5124	168,39
5154	1233
5158	407,21
5162	576,42
5166	563,2
5170	1042,81

I am looking for a format like BEST12. but using commas instead of periods as decimal points. Does that exist? If not, is there another easy way to read these numbers properly without transforming the numbers to character variables and exchange "," to "."? (I have 200 of these variables, so i need something more "practical").

Thank you very much in advance!

FCOPY in scheduler copies file as read only, but read/write when run manually

AviS — Fri, 08 May 2020 15:45:31 GMT

Hello!!

I have a SAS program which when I run manually copies file with read/write access but when run in scheduler in SAS Server copies file only with read access.

How can I resolve it to copy files with read/write access even when run in SAS server scheduler?

data _null_;

rc=fcopy('src', 'dest');

run;

Uploading and copying file to and from SFTP site

mlogan — Fri, 08 May 2020 02:53:33 GMT

Hi All,

I am trying to upload as well as retrieve a file from my pc to the SFTP site. Can someone please tell me what will be the sas code for both of these.

Thanks,

"Merge" several variables

Mathis1 — Thu, 07 May 2020 14:12:05 GMT

Hello,

I would like to merge into one variable, the values of several variables provided that only one variable appears for each observation.

I will be more clear with the picture below.

I would like to put the values of Modele, Energie and Puissance, in one variable where the values follow each other. For instance :

Modele 1

Modele 2

Modele 3

Puissance 1

Puissance 2

Puissance 3

Energie 1

Energie 2

Energie 3

Thank you 🙂

Dynamic Data Quality Job to handle any type of Source File

avvy — Sat, 02 May 2020 09:50:08 GMT

Dear Users,

I have a business scenario, where I will have to process lot of tables from source storage (files are in form of CSV) for standardization i.e. Demographic standardization etc.

The DQ job has to understand the Columns and then pick the column which has demographic values (identification) and further standardize those columns.

The challenge are as below:

1. The job has to identify the number of files in the folder location to be processed and the file names

2. The job has to identify what columns are there in individual tables and which columns refer to demographic values

3. Standardize the identified columns

4. Loop the entire process.

The major challenge is that all the files in the source can have any number of columns and can consist of different columns as compared to the previous or next file in the source folder.

Any ideas on this will really be helpful.

Thanks & Regards,

Abhishek Pathak

Merging SAS datasets

GreenTree1 — Wed, 29 Apr 2020 03:21:21 GMT

Hello Everyone

I have two SAS datasets

dataset 1. dataset 2

1. 4

2 5

3 6

I want to stack these on top of each other so that the final dataset looks as follows.

Final dataset

I appreciate your help.

Business Data Network - Importing data structure already existing in Metadata

Sommeit — Tue, 28 Apr 2020 15:29:55 GMT

Question from the newbie in SAS:

- SAS9.4M6

- SAS BDN 3.3

Here my business requirement:

I need to load an enterprise data model in SAS BDN (MSSQL, Oracle, mySQL Hadoop). BDN would become the main platform for data architect to manage the comapny datamodel.

To do so, I need to import all my tables already loaded in Mgt Console:

- Tables: Table Name, term:table, Associated Items(table in meta)

- Columns: Column names, term column, Parent(tables) and associated items (from SAS meta)

All additional attributes will be manage manually by the business (data Steward)

(e.g. Description, Requirement, from term: Data Owner, Data Steward, Data Custodian, ...)

Base on my reseach, I start to believe there is no existing method proposed by SAS to perfor this load. I would need to build my own XML builder to prepare my data model import.

Question:

1- Is there a more mature method to load this data if it already include in SAS metadata?

2- if not, what would be the best way to extract the metadata (with IDs) from a shared data if I would like to develop a script to "stitch" my schema to my SAS meta elements.

How to drop specific part of the text

PatrykSAS — Tue, 28 Apr 2020 14:52:01 GMT

Hello, I came across the following problem in the example data:

003

0101

150

00070

My case is to get ID without any '0' only at the beginning, so my result should be:

101

150

How can I achieve that? Have a nice day 🙂

multivaraite data to univariate with varaible names as count

sapasupu — Sat, 25 Apr 2020 22:12:38 GMT

Hello everyone,

I have a data structured as

I want to convert it into univariate with a new variable as below

How can I able to achieve that? Tried MV to UV conversion but the Item variable displays only the counts of the Items but not the variable names. Also, PROC TRANSPOSE didn't work. Any clues or help will be appreciated.

Recode daily date data

srobinson5 — Fri, 24 Apr 2020 20:04:18 GMT

Dear SAS users,

I am trying to analyze daily data from a longitudinal study. I would like to code the calendar dates into study week and days (i.e., # of weeks and days they've been enrolled in the study since the first visit). All participants have varying start dates and varying number of days they were in the study. Currently the data I have look like this:

StudyID	Date	Steps	Goal
1	4/20/2020	3001	3000
1	4/21/2020	2987	3000
1	4/22/2020	4313	3500
1	4/23/2020	4380	3500
2	3/13/2020	6098	6200
2	3/14/2020	7022	6200
2	3/15/2020	5980	6400
2	3/16/2020	5750	6400

I would like for it to look like this:

StudyID	StudyWeek	StudyDay	Steps	Goal
1	1	1	3001	3000
1	1	2	2987	3500
1	1	3	4313	3500
1	1	4	4380	3000
2	1	1	6098	6200
2	1	2	7022	6200
2	1	3	5980	6400
2	1	4	5750	6400

Typically, I would post code I have that I need help with, but I don't know where to even start to do this. Any help is very much appreciated!

Best,

Stephanie

Document Conversion (.xlsx to .txt) in Data Management Studio 2.5

CAHarbison — Fri, 24 Apr 2020 16:47:24 GMT

I am trying to write a quick Data Job that will convert an .xlxs file to a delimited .txt file.

I am using the Document Conversion Node for my input file and Document Extraction node and then output to a text file.

Attached is a sample of the output that has been converted to Excel. I cannot figure out why the unique lines continuously repeat. A file of 300,000 records explodes to several million as a result. I reviewed the SAS documentation for this process and am doing everything as it says to. Unfortunately, I cannot find help anywhere else.

I would appreciate any guidance on making this perform as I expect. Thank you.

The data specification could not be created

Aiman — Fri, 24 Apr 2020 12:05:54 GMT

Hi I'm trying to import csv file from my local computer to model studio and create a forecasting project, but unfortunately an error message occur stated that my data specification could not be created: The input data has no valid candidates for the time variable. Specify a table with a column that has a valid date format. Kindly assist me on how to solve the issues. Im sure that my csv file got the date column but somehow it was not recognized by SAS. Below is the picture of my dataset and error message.

error messagedataset from my csv

Output both matching and non-matching rows in a single table

AshJuri — Wed, 22 Apr 2020 15:52:04 GMT

How can I adjust this working code so the observations in column "patient" from table "enroll" that don't match the observations in column "patientID" from table "calendar" are output into another table? In addition, would it be possible to output both matching observations and non-matching observations in the same table? The current working code only returns a table of matching observations based on "patient" and patientID", and "dateassigned" and "datetracked".

data enroll;
    input patient status :$12. dateassigned &:anydtdte.;
    format date yymmdd10.;
    datalines;

500-001   enrolled    01-jan-2019      
500-002   enrolled    15-jan-2019     
500-003   removed     23-Jan-2019     
500-004   enrolled    05-feb-2019     
500-005   enrolled    17-feb-2019     
587-001   enrolled    20-feb-2019
587-002   enrolled    25-feb-2019
587-003   enrolled    03-mar-2019
594-001   enrolled    04-feb-2018
594-002   enrolled    09-feb-2018
648-001   enrolled    15-mar-2019
648-002   enrolled    22-mar-2019
648-003   enrolled    27-mar-2019
648-004   enrolled    30-mar-2019
;

data calendar;
    input visitnumber patientID :$12. datetracked &:anydtdte.;
    format date yymmdd10.;
    datalines;

500 500-001-rdf   01-jan-2019      
500 500-002-fgh   15-jan-2019     
500 500-003-ehd   23-Jan-2019     
500 500-004-ern   05-feb-2019     
500 500-005-qmd   17-feb-2019     
587 587-001-wcs   20-feb-2019
587 587-002-qlc   25-feb-2019
587 587-003-qhr   03-mar-2019
594 594-001-qwn   04-feb-2018
594 594-002-agj   09-feb-2018
648 648-001-wuf   15-mar-2019
648 648-002-qbf   22-mar-2019
648 648-003-olr   27-mar-2019
648 648-004-wmf   30-mar-2019
;

proc sql;
	create table want as
		select 
			enrl.*
			, cal.*
			from 
				enroll enrl
				, calendar cal
				where
					enrl.patient = substr(cal.patientID,1,8) and
					enrl.dateassigned = cal.datetracked
	;
quit;

Set unmapped fields to null/different fields on input to output?

MRDM — Tue, 21 Apr 2020 19:56:45 GMT

Hi, So I'm doing this in DI Studio although as User Written Code. Hopefully this makes sense, there's an example at the end to hopefully help.

The situation is each supplier needs different fields (from a selection of ~50) but all suppliers get the same output delimited file, just the fields they don't need are blank. I had it working when each supplier just needed their fields but we now need everyone to get the same template, with just their fields populated. For example one supplier may need everyones Name, Address, DoB, another might just need Names and DoBs etc. It'll be a different combination for each and even the order may change (field names will match though) but the final output will be a fixed order and field list.

I've setup the 1st part, so I have a UWC transformation with my full table as an input and a variable of the fields they need, it applies this (using a data step and keep) and gives me an output table of just the fields they are entitled to.

The second part I'm struggling with. So far I've tried a data step that sets the output with the full field list and all attribs which is fine, after that I use the below data step hoping it would just map the ones needed into my table and leave the rest as null, it didn't, it errors due to them being missing on the input:

data &_output.;
set &_input.;
run;

However trying to open the output table fails due to all the missing columns, what's needed is for it to do that and set all the unmapped fields to nulls. Essentially the process is:

Full Table > UWC - Filtered view of customer required fields > UWC - Map filtered list back, set non required to null > Full table (which will be exported to a delimited file), we can go straight to that and skip the final table if needed.

Any ideas thanks? Hopefully this helps, the filtered table will be different everytime:

In Table (50 fields):

Name	Address	Postcode	DoB	Colour
John Smith	1 made up street	1MDUS	01/01/2000	Blue
Jane Doe	2 Fake Place	2FAPL	31/12/2005	Red

Filtered view (diff for each supplier and field order can change but names will always match, 1-50 fields)

Postcode	DoB	Name
1MDUS	01/01/2000	John Smith
2FAPL	31/12/2005	Jane Doe

Output (50 fields), can be sas table, ultimately will be a delimited file output):

Name	Address	Postcode	DoB	Colour
John Smith		1MDUS	01/01/2000
Jane Doe		2FAPL	31/12/2005

Join two tables together based on similar but not equal columns in SAS

AshJuri — Tue, 21 Apr 2020 14:26:12 GMT

data enroll;
    input patient status :$12. dateassigned &:anydtdte.;
    format date yymmdd10.;
    datalines;

500-001   enrolled    01-jan-2019      
500-002   enrolled    15-jan-2019     
500-003   removed     23-Jan-2019     
500-004   enrolled    05-feb-2019     
500-005   enrolled    17-feb-2019     
587-001   enrolled    20-feb-2019
587-002   enrolled    25-feb-2019
587-003   enrolled    03-mar-2019
594-001   enrolled    04-feb-2018
594-002   enrolled    09-feb-2018
648-001   enrolled    15-mar-2019
648-002   enrolled    22-mar-2019
648-003   enrolled    27-mar-2019
648-004   enrolled    30-mar-2019
;

data calendar;
    input visitnumber patientID :$12. datetracked &:anydtdte.;
    format date yymmdd10.;
    datalines;

500 500-001-rdf   01-jan-2019      
500 500-002-fgh   15-jan-2019     
500 500-003-ehd   23-Jan-2019     
500 500-004-ern   05-feb-2019     
500 500-005-qmd   17-feb-2019     
587 587-001-wcs   20-feb-2019
587 587-002-qlc   25-feb-2019
587 587-003-qhr   03-mar-2019
594 594-001-qwn   04-feb-2018
594 594-002-agj   09-feb-2018
648 648-001-wuf   15-mar-2019
648 648-002-qbf   22-mar-2019
648 648-003-olr   27-mar-2019
648 648-004-wmf   30-mar-2019
;

Hi all, I am trying to join these two these tables together. I want the columns to align where the column “patient” in table “enroll” matches the column “patientID” in table “calendar”. "patient" and "patientID" are similar but not equal. In addition, the column “dateassigned” in table “enroll” should match the column “datetracked” in table “calendar”. Since this is only a snippet of a larger dataset, the columns should align together. However, this may not be the case in the larger dataset, so I am trying to highlight where the columns from both tables do not align. Thank you in advance.

SAS Viya Data Explorer

suyashucd — Sun, 12 Apr 2020 15:25:03 GMT

Can anyone suggest how to increase the size of data explorer? I have attached the document for it

Importing multiple CSV files using generic SAS code

jinendergulati — Thu, 09 Apr 2020 05:04:02 GMT

Hi,

I'm trying to import multiple csv files in SAS DI Studio 4.9. Each csv file has different layout and these contain a trailer record at the end of each file. The layout of the trailer record is common across each file which contain the information like number of records in the file, time of generation etc. Preference is to create only 1 job to for this purpose and not have multiple branches of transformation in the job for each type of csv file. I believe it is feasible to read a csv with unknown column layout using PROC IMPORT but the trailer record is causing issues.

Is it feasible to have a generic code to read multiple csv files with trailer records?

Thanks,

Jinender

Merging datasets

mezerji — Wed, 08 Apr 2020 20:41:57 GMT

How can I set these data sets with names: Fit_1, Fit_2, Fit_5, ..., Fit_1000.

I can not run with this:

##############################

data fit;

set Fit_1 - Fit_100;

output;

run;

##############################

Error: work.fit_3 does not exist.

Return a table based on simply query

AshJuri — Wed, 08 Apr 2020 19:06:41 GMT

Hi, I am trying to perform a query that returns the dates less than/prior to the current date in the column "date". This should form a new column called "past".

data have;
input Subject Type :$12. Date &:anydtdte.;
format date yymmdd10.;
datalines;

500   Initial    15 AUG 2017      
500   Initial    15 AUG 2017    
500   Followup   15 AUG 2018    
428   Followup    15 AUG 2018     
765   Seventh     3 AUG 2018      
500   Followup    3 JUL 2018      
428   Initial     3 JUL 2017    
765   Initial     20 JUL 2019   
610   Third       20 AUG 2019    
610   Initial     17 Mar 2018    
;

Filtering values prior to query

AshJuri — Wed, 08 Apr 2020 17:40:49 GMT

Hi, I want to calculate the current value divided by the minimum value in column "measurement" from visits prior to the current visit in column "type". However, first I need to use the column "jsw" to filter for visits prior to the current visit value, and then calculate the minimum from those prior visits. This would require first searching for the minimum" jsw" value, then looking for records specific to the where "jsw" is less than the minimum "jsw" value found and then from that subset of records, pull the minimum "measurement" value for the division calculation. However, I cannot figure out to implement this in my current working code. Any solutions to this matter would be appreciated.

data have;
	input Subject Type :$12. Date &:anydtdte. jsw procedure :$12. measurement;
	format date yymmdd10.;
	datalines;

Subject Type      Date                jsw                procedure     measurement      
500   Initial    15 AUG 2017            6                   Invasive        20        
500   Initial    15 AUG 2017            9                   Surface         35        
500   Followup   15 AUG 2018            8                   Invasive        54       
428   Followup    15 AUG 2018          56                   Outer           29        
765   Seventh     3 AUG 2018           12                   Other           13     
500   Followup    3 JUL 2018           23                   surface         98    
428   Initial     3 JUL 2017           34                   Outer           10    
765   Initial     20 JUL 2019          4                    Other           19     
610   Third       20 AUG 2019         58                    Invasive        66   
610   Initial     17 Mar 2018         25                    Invasive        17    

PROC SQL;
    create table want as
    select a.*,
        ((select measurement as lastmeasurement
         from have
         where Subject = a.Subject and procedure= a.procedure 
         having jsw = max(jsw)) -min(a.measurement))/ min(a.measurement)*100 as WantedPercent
    from have as a
    group by Subject, type
    order by Subject, type, date;
QUIT;

Dividing within a column based on other columns matching

AshJuri — Tue, 07 Apr 2020 23:39:18 GMT

Hi all, I am trying to calculate the current measurement in column “Total" minus the lowest measurement previously recorded in column "Total" where the current measurement in column “Total” corresponding to the value in column "Trade" is less than(<) the minimum measurement in column “Total” corresponding to the value in column "trade", and if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. To emphasize, the minimum value must be a previously recorded. If the measurement is less than the current measurement but was not recorded previously (according to the “date” column), it does not quality to be subtracted from the current measurement. An example of the output is provided below.

data Have;
	input Subject Type :$12. Date &:anydtdte. Trade Procedure :$12. Measurement;
	format date yymmdd10.;
	datalines;

Subject Type      Date                Trade                Procedure     Total      
500   Initial    15 AUG 2017            6                   Invasive        20        
500   Initial    15 AUG 2017            9                   Surface         35        
500   Followup   15 AUG 2018            8                   Invasive        54       
428   Followup    15 AUG 2018          56                   Outer           29        
765   Seventh     3 AUG 2018           12                   Other           13     
500   Followup    3 JUL 2018           23                   surface         98    
428   Initial     3 JUL 2017           34                   Outer           10    
765   Initial     20 JUL 2019          4                    Other           19     
610   Third       20 AUG 2019         58                    Invasive        66   
610   Initial     17 Mar 2018         25                    Invasive        17     

*Example of Output;

Subject Type      Date                Trade                Procedure     Total        Output
500   Initial    15 AUG 2017            6                   Invasive        20        20/20
500   Initial    15 AUG 2017            9                   Surface         35        35/35
500   Followup   15 AUG 2018            8                   Invasive        54        54/20
428   Followup    15 AUG 2018          56                   Outer           29       29/10
765   Seventh     3 AUG 2018           12                   Other           13       13/19
500   Followup    3 JUL 2018           23                   surface         98       98/35
428   Initial     3 JUL 2017           34                   Outer           10       10/10
765   Initial     20 JUL 2019           4                   Other           19       19/19
610   Third       20 AUG 2019          58                   Invasive        66       66/17
610   Initial     17 Mar 2018          25                   Invasive        17       17/17

Conditional PROC SQL

RicHen — Tue, 07 Apr 2020 15:34:00 GMT

Hi there!

I am trying to call a PROC SQL or another based on an input variable.

My input variable is like YYYYMM, and if MM is equal to 01 then I do a new table else I need to merge with the table from the month before.

Is there anyway to do this?

I tried but I keep failing it doesn't create a table.

Thanks in advance.

The code I tried was something like:

%let month=%substr(&delta,5,2);
%macro january;
proc sql;
	create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as 
 	select *
		from WORK.SOLV_RECEITA_EMITIDA_MES;
quit;
%mend janeiro; 
%macro outros;
proc sql;
	create table WORK.RH_SOLV_RECEITA_EMITIDA_&delta as 
	select * FROM WORK.SOLV_RECEITA_EMITIDA_OLD_MES
	outer union corr 
	select * FROM WORK.SOLV_RECEITA_EMITIDA_MES;
quit;
%mend outros;
data _null_;
	if &month = '01' then call execute('%janeiro');
	if &delta <> '01' then call execute('%outros');
run;

How to join two columns from one table to a different table based matching criteria

AshJuri — Tue, 07 Apr 2020 00:15:04 GMT

Hi, I am trying to join the columns "Type2" and "Measurement2" from table "Update" to the table "Have". I want the columns to align where column "Subject1" in table "Have" matches column "Subject2" in table "update", and column "Procedure1" in table "Have" matches column "Procedure2" in table "Update". Thank you in advance.

data Have;
	input Subject1 Type1 :$12. Date1 &:anydtdte. Procedure1 :$12. Measurement1;
	format date yymmdd10.;
	datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35   
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19  
610   Initial     17 Mar 2018     Invasive    17 
;

data Update;
	input Subject2 Type2 :$12. Date2 &:anydtdte. Procedure2 :$12. Measurement2;
	format date yymmdd10.;
	datalines;

500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
610   Third       20 AUG 2019     Invasive    66  
;

Dataflux Conditional Expression with ODBC connection

Vinz867 — Mon, 06 Apr 2020 14:43:53 GMT

Hi all,

I am new in Dataflux and I am trying to figure out how to do a conditional logic with an ODBC connection using multiple columns from the external data source. There are multiple columns in the RULES table and I need to use them in a conditional logic.

This is what I have so far.

static dbconnection db1 static dbstatement stmt1 dbcursor curs1 boolean retval1 if isnull(db1) then db1 = dbconnect('DSN=MS Access Database;DFXTYPE=ODBC') if isnull(stmt1) then begin stmt1 = db1.prepare("SELECT * FROM "RULES" WHERE ISALPHA = ?") stmt1.setparaminfo(0, 'string', 5) end <<missing comparison>> if retval1 == true then return true else return false

Update/insert the record and maintain the history

David_Billa — Sun, 05 Apr 2020 08:20:17 GMT

We've a process where clients will update the file (say sales details) when there is a need and they intimate us about the change. So our job is to load those updated records to SQL DB table by maintaing the history.

For the first time while loading the data to SQL we just did a 'proc append' to SQL table after reading the file via data step infile Statement. However this method will not work to track the history in SQL when there is a update in the file.

I'm not Aware of SCDs as well. We are not using any Primary/foreign key stuff in SQL.I would like to understand how I can tackle the situation to update the history. I tried with 'Proc SQL update' and 'If first. and last.' but it didn't helped either. Appericiate your help here. I'm OK with any approach to implement this exercise.

Assume I've a file like this.

Id	Name	Sales	Transaction	Example Values	Act_Ind	Valid_From	Valid_To
1	Unit	True	6	58001	Y	19000101T000000	99991231T235959
2	Key	True	6	121216	Y	19000101T000000	99991231T235959
3	Value	True	18	820595,2	Y	19000101T000000	99991231T235959

Assume I've received an update to the file like this. If you notice Transaction value has changed for Key from 6 to 8.

Id	Name	Sales	Transaction	Example Values	Act_Ind	Valid_From	Valid_To
2	Key	True	8	121216	Y	20200404T000000	99991231T235959

So now I want data like this SQL table.

Id	Name	Sales	Transaction	Example Values	Act_Ind	Valid_From	Valid_To
1	Unit	True	6	58001	Y	19000101T000000	99991231T235959
2	Key	True	6	121216	N	19000101T000000	20200403T000000
2	Key	True	8	121216	Y	20200404T000000	99991231T235959
3	Value	True	18	820595,2	Y	19000101T000000	99991231T235959

Calculation of values that rely on a date variable

AshJuri — Fri, 03 Apr 2020 17:12:42 GMT

Hi, I am trying to calculate the value of the last measurement taken (according to the date column) divided by the lowest value recorded (according to the measurement column) if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. The the calculation would be produced in a new column. I am having trouble with this and I would appreciate a solution to this matter.

data Have;
	input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
	format date yymmdd10.;
	datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35 
500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19 
610   Third       20 AUG 2019     Invasive    66 
610   Initial     17 Mar 2018     Invasive    17 
;

VLOOKUP dataset with file

Vinz867 — Fri, 03 Apr 2020 13:07:48 GMT

I have been trying to solve this for awhile and I really need help with DF.

I have file with 5 columns, I need to validate this file against a whitelist file using an expression.

I cannot do a left or right join because it will create additional rows that are not in the original file.

Inner join will remove rows that I need. I also tried a seekbegin() to the whitelist file but I don't think I'm doing it right. Any help will be appreciated!

Pre-expression:

string VALIDITY

file f
string curr_line
string COLUMNA
string COLUMNB
integer COLUMNC

f.open("\\whitelist.csv","r")

Here is the expression:

begin
f.seekbegin(0)
curr_line= f.readline()
while (curr_line)
parse(COLUMNA,",",COLUMNB,",",COLUMNC)
begin
curr_line= f.readline()
while isnull(VALIDITY)
begin
<conditional logic>
then VALIDITY = 'VALID'
else VALIDITY = 'INVALID'
end
end
end

Creating a new variable using two dataset with different var names and observations

mghamari63 — Wed, 01 Apr 2020 13:26:38 GMT

Dear all,

Assume there are two datasets as follow:

data one;

input A B;

cards;

4 6

3 2

run;

data two;

input C D;

cards;

1 3

2 5

3 8

4 7

run;

As you see neither variables nor obs are equal.

I would like to create another variable E in dataset one which is equal to var D where A=C. Finally, I am looking for a dataset for example like this:

A B C D E

4 6 . . 7

3 2 . . 8

. . 1 3 .

. . 2 5 .

. . 3 8 .

. . 4 7 .

So I can do some analyses on A and E.

Please let me know if I can use SAS for this reason.

Thank you!

SQL to Dataflux (NOOB)

Vinz867 — Tue, 31 Mar 2020 17:09:34 GMT

Hi,

I need some help converting this SQL to a Dataflux expression. I am trying to do a comparison between 2 files and old code in SQL used this to compare it.

LEFT(LTRIM(RTRIM(cast(COLUMNA as varchar(1000)))),LEN(COLUMNB)) = COLUMNA

LEFT(LTRIM(RTRIM(SUBSTRING( LTRIM(RTRIM(CAST(COLUMNA as varchar(1000)))) , LEN(COLUMNC) + 1 ,LEN(cast(COLUMNA as varchar(1000)))- (LEN(@COLUMNC) + 1) ))),LEN(COLUMNB) = COLUMNA

Column A is from File A

Column B is from File B.

Column C is from File B.

Please help!

SAS Data Integration Studio - parametrizing job using a .csv file

TomekQ — Tue, 31 Mar 2020 09:15:05 GMT

Hi!

How can we create parameters for SAS DI jobs by reading a .csv file. For example, I would like to select all rows with where clause with parameters from the .csv file.

Regards

SAS Data Integration Studio - transforming all tables in a library

TomekQ — Tue, 31 Mar 2020 07:25:03 GMT

Hi!

I need to perform a transformation on all tables in a library in SAS DIS. How can I do it? Let's say that I want to delete rows based on a certain condition in every table.

Regards

Convert SAS v 6.12 VAX files to windows

rlmacdonald — Sun, 29 Mar 2020 18:12:46 GMT

I have a clinical trial data from the 1980s that is in SAS v6.12 and was created on the old DEC VAX system (DEC model 4000 MODEL 610) and I want to convert it to be able to be read in Windows. SAS technical support said they don't have any versions of SAS like this anymore. Any suggestions? Thanks.

RL Macdonald, MD

Department of Neurological Surgery

UCSF Fresno

Convert Character Date to Numeric

AshJuri — Tue, 24 Mar 2020 14:56:09 GMT

Hi everyone, I am trying to convert a date variable that is stored as a character variable into a numeric variable. However, the date variable is in the format "02 AUG 2017". I want the result outputted as a new date variable that is numeric.

data want;
  set have;
  date=input(character_date,date10.);
  format date date10.;
run;

Nothing that I have tried has worked. I would appreciate any assistance. Thank you in advance.

Calculating percent change based on matching columns

AshJuri — Mon, 23 Mar 2020 17:20:30 GMT

Hi everyone,

data have;
input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;

500   Initial    15 AUG 2017      Invasive    20 
500   Initial    15 AUG 2017     Surface      35 
500   Followup   15 AUG 2018     Invasive     54 
428   Followup    15 AUG 2018      Outer      29 
765   Seventh     3 AUG 2018      Other       13 
500   Followup    3 JUL 2018      Surface     98 
428   Initial     3 JUL 2017     Outer        10 
765   Initial     20 JUL 2019     Other       19 
610   Third       20 AUG 2019     Invasive    66 
610   Initial     17 Mar 2018     Invasive    17 
;

data want (drop=rc _Measurement);
   if _N_ = 1 then do;
      declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");
      h.definekey ('Subject');
      h.definedata ('_Measurement');
      h.definedone();
   end;

   set have;
   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();
   NewMeasurement = ifn(Measurement=., ., sum (Measurement, -_Measurement));
run;

This code currently subtracts the initial (Type column) measurement from the other measurements if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match. It produces a new column with the calculations. This is the absolute change in measurements.

I want to add to this code so that the value of the latest (date column) measurement taken is divided by the lowest measurement recorded if two values in the “SUBJECT” column match and two values in the “PROCEDURE” column match and the calculation is produced in a new column. This is also known as the percent change from nadir. Thank you in advance!

SAS Error: Subquery evaluated to more than one row

AshJuri — Thu, 19 Mar 2020 20:16:49 GMT

I am getting the message "ERROR: Subquery evaluated to more than one row. I have posted the working code below. I would like to know how this error can be resolved while still having the program run as intended

data have;
input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;

500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018     Invasive     54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018      Surface      98 
428   Initial     23 FEB 2018     Outer        10 
765   Initial     20 AUG 2019     Other        19 
610   Third       21 AUG 2018     Invasive     66 
610   Initial     27 Mar 2018     Invasive     17 
999   Dummy       17 mar 2020     Some          1
999   Dummy       18 mar 2020     Some          2
999   Dummy       19 mar 2020     Some          3
;

proc sql;
create table want as
select *,
    (select max(measurement) 
     from have 
     where subject=a.subject and type=a.type and procedure=a.procedure 
     having date = max(date)) / min(measurement) as ratio
from have as a
group by subject, type, procedure
order by subject, date;
quit;

. Thank you in advance.

Working with a date column corresponding to values in another

AshJuri — Thu, 19 Mar 2020 15:52:14 GMT

Hi, I would like to add on to this code so that the value of the last measurement according to the "date" column in the "measurement" column is divided by the lowest value recorded in the measurement column and the result forms a new column. The current working code adds a column that subtracts the initial measurements from the other measurements in the "measurement" column if the "subject", "type" and "procedure" columns match.

data have;
input Subject Type Date $ 5-12 Procedure $ 15-22 Measurement;
datalines;
500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018   Invasive   54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018     Surface     98 
428   Initial     23 FEB 2018        Outer        10 
765   Initial     20 AUG 2019        Other        19 
610   Third       21 AUG 2018      Invasive    66 
610 Initial       27 Mar   2018       Invasive     17 
;
data want (drop=rc _Measurement);
   if _N_ = 1 then do;
      declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");
      h.definekey ('Subject');
      h.definedata ('_Measurement');
      h.definedone();
   end;

   set have;
   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();
   NewMeasurement = ifn(Measurement=., ., sum (Measurement, -_Measurement));
run;

Add new column with updates reflected without affecting missing data

AshJuri — Wed, 18 Mar 2020 21:22:12 GMT

Hi everyone, below is my working program that I would like to update.

data have;input Subject Type $ 5-12 Procedure $ 15-22 Measurement;datalines;
500 Initial   Invasive  20 
500 Initial   Surface   35 
500 Followup  Invasive  54 
428 Followup  Outer     29 
765 Seventh   Other     13 
500 Followup  Surface   98 
428 Initial   Outer     10 
765 Initial   Other     19 
610 Third     Invasive  66 
610 Initial   Invasive  17 
;
data want (drop=rc _Measurement);
   if _N_ = 1 then do;
   declare hash h (dataset : "have (rename=(Measurement=_Measurement) where=(Type='Initial'))");      h.definekey ('Subject');      h.definedata ('_Measurement');      h.definedone();
   end;

   set have;   _Measurement=.;

   if Type ne 'Initial' then rc = h.find();   Measurement = sum (Measurement, -_Measurement);run;


The goal is to subtract the numbers in the "MEASUREMENT" column based on the "SUBJECT", 
"TYPE" and “PROCEDURE” columns. If two values in the “SUBJECT” column match and two values 
in the “PROCEDURE” column match, then the initial measurement should be subtracted from the 
other measurement. For example, the initial measurement in row 1 (20) should be subtracted from 
the followup measurement in row 3 (54) because the subject (500) and procedure (Invasive) match.
Furthermore, the initial measurement in row 8 (19) should be subtracted from the seventh 
measurement in row 5 (13) because the subject (765) and procedure (Other) match. 
The result should form the "OUTPUT" column. 

How can I account for missing values in the "measurement" column and 
keep them without being affected in the "output" column?? 
In addition, I noticed that this program updates the "measurement" column. 
How can I have the updates be created in a new column called "output" while keeping the "measurement" column?

Connecting to two databases on same SQL server via UNIX SAS

TBarker — Wed, 18 Mar 2020 17:21:11 GMT

Is it possible to define one DSN in the odbc.ini file on SAS UNIX and use it for multiple databases on that host?

We have two databases that are on the same host. One of the databases was previously defined in the odbc.ini file on our SAS UNIX server and was working fine, but now there is a second database on the same server and someone wants to connect to it via SAS. I was hoping that we could simply change the DSN definition to change the DATABASE=abc to DATABASE= and then use the same DSN in LIBNAME statements for both databases by adding DATABASE=whatever to the libname statement, rather than having to define two different DSNs for the same host.

Works:

The DSN in the odbc.ini file is IP and the following LIBNAME statement works if we have DATABASE=IP in the DSN definition in the odbc.ini file: LIBNAME IP SQLSVR Datasrc=IP USER=IPME PASSWORD=XXXX ;

Fails:

If we change to DATABASE= in the DSN definition and try to connect to the two databases by adding DATABASE=[db] in the LIBNAME statement, both LIBNAME statements fail. These are the errors we get - different error for each LIBNAME statement:

LIBNAME IP SQLSVR Datasrc=IP Database=IP USER=IPME PASSWORD=XXXX ;

ERROR: CLI error trying to establish connection: [SAS][ODBC 20101 driver][20101]Login failed for user 'IPME'.

LIBNAME OCM SQLSVR Datasrc=IP Database=OCM SCHEMA=dbo USER=ocmme PASSWORD=XXXX;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the value of InstallDir in your odbc.ini.

It seems like it should be possible to not have to create a new DSN entry for every database if they are on the same host, but perhaps not, since I haven't been able to find much helpful online about this and our testing thus far has failed. I'm hoping someone has some sparkling insight into making this work with only one DSN entry. If not, or it's just not possible on UNIX, then we will simply create a second DSN entry for the second database.

Vendor reconciliation

Samyuktha1 — Mon, 16 Mar 2020 04:59:15 GMT

Can anyone help me with vendor reconciliation listings in clinical trail data management . With code especially.

Need help in crrecting a job

swathiprasad — Sun, 15 Mar 2020 00:28:42 GMT

Hi ,

I am bit new to sas DI Studio.

In our company , we develop jobs in sas DI and promote to MainFrame.

I have to change a job in sas DI , When I compared the SAS DI code and Mainframes code ,it is not matching at all.

Seems like somebody has messed up the job in DI(Mainframe code is not messed up) .

As the job is not reliable, I created a new job.

When I compared the new job developed in sas DI(leaving the change) and the existing job ( in Mainframes) they both are not matching at all.

Need help in understanding the changes between the 2 codes.

Psrskwm3.sas is the ---Mainframe code

the later is the new sas DI job(in the new job the only change should be

LIBNAME access V8 "dwgip.dwh.views.full" disp=shr ;

where as in Mainframe it shoul be

LIBNAME access V8 "dwgip.dwh.views" disp=shr ;

Conditional Logic in Data Integration Studio

DavidPhillips2 — Fri, 13 Mar 2020 20:46:31 GMT

Is there a way to conditionally run a transformation in Data Integration Studio? I am working on automating overnight edit checks. However, I do not want to spam recipients if there are no edit records in a table. I am using the Publish to Email transformation to send data in a table. I see some objects in Data Integration Studio that look like there for conditional logic but I do not know how they work. I am using Data Integration Studio Version=4.902.

Indicator variable that tracks the year before a variables changes and the year it changes

r4321 — Fri, 13 Mar 2020 17:45:41 GMT

Trying to create an indicator variable that tracks the year before a different variable changes and the year it changes by creating a dummy = 1. See example.

1999	222	ABC CO	0
2000	222	ABC CO	1
2001	222	ABC CO	0
2002	222	ABC CO	0
2003	222	ABC CO	0
2004	222	ABC CO	0
2005	222	ABC CO	0
2006	222	ABC CO	1

Want

1999	222	ABC CO	0	1
2000	222	ABC CO	1	1
2001	222	ABC CO	0	0
2002	222	ABC CO	0	0
2003	222	ABC CO	0	0
2004	222	ABC CO	0	0
2005	222	ABC CO	0	1
2006	222	ABC CO	1	1

Your help would be very much appreciated!

Thanks

Count months from date to the end of the year

PatrykSAS — Fri, 13 Mar 2020 15:52:22 GMT

Hi, I'd like to see a number of months left from the date to the end of the year.

I know to count month I can use intck('month', 'start', 'end', 'D') but how to get the "end" date when it's not given. I dont know how to pass the 'end' argument. Only what I got is the start date.

I would like to see:

date months

01JUN2019 7

01APR2020 9

Have a good day 🙂

SQL Passthrough DB2 - WITH clause problem

saraimi — Thu, 12 Mar 2020 19:45:48 GMT

I cannot get this query to run. I've tried a lot of variations on this query - mostly trying either the WITH clause or an inline query. I can use the SQL passthrough to DB2 with simpler queries, so I've left it out of the examples. I've googled the error message, DB2 "WITH Clause", "common table expression", "CTE", and various combinations, and none of the dozens of results had anything that helps me resolve the issue. I've also tried the inner query alone - that works. I've tried these variants in another query tool, and have gotten the same results. I know that means it's not a SAS problem, but I'm hoping someone can help, or point me to any useful documentation.

WITH clause VERSION:

WITH inner_query AS

(SELECT oc.clm_cd, p.prod_ser_no, p.bld_date, p.vin,
p.in_srvc_date, p.in_srvc_trk_mlg,
year(p.in_srvc_date) as in_service_year,
case when p.prod_type_cd = 'ENGINE'
then p.prod_ser_no else '' end as eng_serial_number,
case when p.prod_type_cd = 'ENGINE'
   then p.prod_mdl_cd else '' end as eng_model,
p.prod_mdl_cd, p.voc_cd,
p.prod_make_cd, p.prod_mdl_cd as fllc_internal_model,
p.prod_fam_cd, p.chass_mdl_cd, oc.flt_cd,
oc.flt_name, oc.fail_date, oc.trk_mlg_amt,
   case when cfa.new_maj_comp_ser is not null
   then oc.trk_mlg_amt else 0 end as eng_repl_mileage,
   case when cfa.new_maj_comp_ser is not null
   then oc.fail_date else '12-31-2999' end as eng_repl_date
   FROM wty_ddc.claims_fact_ddc_v oc
   INNER JOIN wty_ddc.product_dim_v p
   ON oc.prod_id = p.prod_id
   INNER JOIN wty_ddc.claims_fact_attr_ddc_v cfa
   ON cfa.clm_id = oc.clm_id
   WHERE trk_mlg_amt is not null and trk_mlg_amt > 0
   and prod_type_cd in ('ENGINE','CHASSIS') )

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "clm_cd, prod_ser_no,
bld_date, vin, in_sr" was found following "il_date desc) select". Expected tokens may include:
"<space>". SQLSTATE=42601

(My interpretation is that it objects to the outer query. If I remove the columns list with "select * from inner_query" leaving the order by clause, THIS DOES WORK. But there's no need for the WITH clause in that case - it's superfluous. Anyway, part of the work I want to do here requires that PARTITION OVER feature.)

INLINE QUERY VERSION:

SELECT clm_cd, prod_ser_no, bld_date, vin,   /*POST EDITED TO REMOVE TABLE ALIAS Q. from clm_cd */
            in_srvc_date, in_srvc_trk_mlg,
            in_service_year,
            prod_mdl_cd, voc_cd,
            prod_make_cd, fllc_internal_model,
            prod_fam_cd, chass_mdl_cd, flt_cd,
            flt_name, fail_date, trk_mlg_amt,
eng_repl_mileage,
eng_repl_date,
            max(eng_serial_number) OVER PARTITION BY (vin) as eng_serial_number,
            max(eng_model) OVER PARTITION BY (vin) as eng_model
   FROM (SELECT oc.clm_cd, p.prod_ser_no, p.bld_date, p.vin,
   p.in_srvc_date, p.in_srvc_trk_mlg,
year(p.in_srvc_date) as in_service_year,
case when p.prod_type_cd = 'ENGINE'
then p.prod_ser_no else '' end as eng_serial_number,
   case when p.prod_type_cd = 'ENGINE'
   then p.prod_mdl_cd else '' end as eng_model,
   p.prod_mdl_cd, p.voc_cd,
   p.prod_make_cd, p.prod_mdl_cd as fllc_internal_model,
   p.prod_fam_cd, p.chass_mdl_cd, oc.flt_cd,
   oc.flt_name, oc.fail_date, oc.trk_mlg_amt,
   case when cfa.new_maj_comp_ser is not null
then oc.trk_mlg_amt else 0 end as eng_repl_mileage,
case when cfa.new_maj_comp_ser is not null
   then oc.fail_date else '12-31-2999' end as eng_repl_date
   FROM wty_ddc.claims_fact_ddc_v oc
   INNER JOIN wty_ddc.product_dim_v p
   ON oc.prod_id = p.prod_id
   INNER JOIN wty_ddc.claims_fact_attr_ddc_v cfa
   ON cfa.clm_id = oc.clm_id
   WHERE trk_mlg_amt is not null and trk_mlg_amt > 0
   and prod_type_cd in ('ENGINE','CHASSIS')
   ORDER BY vin, fail_date desc)

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "q.clm_cd, prod_ser_no,
bld_date, vin, in_srvc_date, in" was found following "SELECT ". Expected tokens may include:
"<space>". SQLSTATE=42601

(Same interpretation here - I think the query would work if the outer select statement was just "select * from....", but that's useless.)

I have tried the same thing leaving out the two column specs using PARTITION BY, in case that was ultimately the source of the syntax problem. However, this error code ultimately means the same thing as the earlier one:

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0206N "Q.CLM_CD" is not valid in the context
where it is used. SQLSTATE=42703

>>> I think I could get the work done with several PROC SQL steps creating local temporary tables, but that will make this run much, much slower.

TIA!!

Steve

ODBC Connection Materialized vs Table in Library

MB_Analyst — Thu, 12 Mar 2020 12:27:58 GMT

I am connecting to an Oracle DB via tan ODBC driver in SAS.

LIBNAME tables
ODBC
USER=USER
PASSWORD=*****
DSN="Name"
SCHEMA=SCEMA;

The Oracle database has some tables replicated as a materialized view to speed up processing for some users. When I connect with SAS does the library show a copy of the table, or the materialized view of the table? I would prefer not to use the materialized version as it updates less frequently than I need it.

Set to missing after a specific value

silango — Wed, 11 Mar 2020 23:54:10 GMT

Hi,

I want to create a dataset that looks like data "want" below, for id 1-100. This is for an exercise so the values don't matter, but the data structure needs to follow these rules:

6 rows (time 0-5) for every id.
For each id, once y=1, then next rows will be missing.
For each id, y=0 for rows before y=1.
20% of the ids have y=1.

I have tried a few approaches, but I am running into issues. For example, this attempt goes from wide to long. Within each id, how do I set values after 1 to missing?

Thanks in advance!

data sim1; 
	do id=1 to 100;
	year0=0; 
	year1=1;
	year2=2;
	year3=3;
	year4=4;
	year5=5;
	output; end;
run;

proc transpose data=sim1 out=sim2; 
	by id;  
run;

data sim3; 
	set sim2 (rename=(COL1=year)); 
		y=rand("BINOMIAL", 0.2, 1);
	drop _name_;
run;

data want; 
	input id y time; 
	datalines; 
		1 1 0
		1 . 1
		1 . 2
		1 . 3
		1 . 4
		1 . 5
		2 0 0
		2 0 1
		2 0 2
		2 1 3
		2 . 4
		2 . 5
		;
run;

How to flag observations if all values of a variable within a by group are the same

KPCklebspn — Wed, 11 Mar 2020 21:25:21 GMT

Hi there,

I have a dataset that looks like this

ID varA

1 0

2 1

3 0

3 1

I want it to look like this (i.e. flag=1 if all values of varA are the same within ID by group):

ID varA flag

1 0 1

2 1 1

3 0 0

3 1 0

How would I do this? Thanks

Data Process runs in different times

MBVilela — Wed, 11 Mar 2020 14:09:01 GMT

Hi, Please could somebody help me... I need to do a process job (in Data Flux) with three data jobs linked, so that when I start the Process job (manually), the 1 and 2 data jobs run and the third data job wait 10 minutes to start since the process starts... is possible?

How to sum up the valid responses from some variables ignore the missing values

CynthiaWei — Tue, 10 Mar 2020 18:46:46 GMT

Hi SAS Pros,

I am have a data like this:

Have:

Var1	Var2	Var3	Var4	Var5	Var6
1	2	0	.	.	.
0	.	1	1	1	1
2	0	.	.	2	2
1	2	1	1	2	4

I want to sum up the valid responses from these variables and ignore the missing values.

Want:

Var1	Var2	Var3	Var4	Var5	Var6	Total
1	2	0	.	.	.	3
0	.	1	1	1	1	4
2	0	.	.	2	2	6
1	2	1	1	2	4	11

Thank you in advance for any help!

Best regards,

How to read SAS DIS Jobs Metadata

asd3 — Tue, 10 Mar 2020 01:54:22 GMT

Hi,

I have several DI jobs each taking in different source tables.

e.g:

1) job 1 - table 1, table 2

2) job 2 - table 1, table 3, table 4

3) job 3 - table 4, table 5

Is there anyway i can extract these information into a SAS dataset in the following format?

e.g.:

job_names source_tables
job 1 table 1
job 1 table 2
job 2 table 1
job 2 table 3
job 2 table 4
job 3 table 4
job 3 table 5

datepart() returns zero

tellmeaboutityo — Mon, 09 Mar 2020 20:26:33 GMT

I'm working with a datetime variable, but I want to reduce it to at least a day/month/year variable, or preferably monthyear, so that I can then select only distinct cases of the reduced format. Basically, I want to recode the dates into season/year variables for many observations. However, the datepart() function doesn't seem to be working for me.

The code I'm using is this, where raisedate is the original date variable:

data want; data have;

datetime = raisedate;
format datetime dateampm.;
datejulian = raisedate;
format datejulian julian.;
date = datepart(raisedate);
run;

Produces this:

id raisedate datetime datejulian datepart

1 21AUG2014 01JAN60:05:32:36 AM 14233 0
2 27APR2017 01JAN60:05:48:56 AM 17117 0
3 21AUG2014 01JAN60:05:32:36 AM 14233 0
4 21AUG2014 01JAN60:05:32:36 AM 14233 0
5 21AUG2014 01JAN60:05:32:36 AM 14233 0
6 21AUG2014 01JAN60:05:32:36 AM 14233 0
7 21AUG2014 01JAN60:05:32:36 AM 14233 0
8 21AUG2014 01JAN60:05:32:36 AM 14233 0
9 12MAR2019 01JAN60:06:00:20 AM 19071 0
10 26APR2019 01JAN60:06:01:05 AM 19116 0

So, for some reason I can reformat the date time, but the datepart() function isn't working.

Perhaps related to this issue, is that if I select distinct on the raisedate variable (i.e. without an id variable), I still get multiple indistinct entries, such as from above, even with the various formats broken out:

21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0
21AUG2014 01JAN60:05:32:36 AM 14233 0

Clearly, they are not obviously distinct depending on format. I've ran into issues with lack of rounding hidden from user view before in SAS, but I'm not sure if that's the issue here, or how I would "round" a date to resolve it.

Any recommendations for what to do? Many thanks!

Remove duplicates with higher values within a variable

kp19 — Mon, 09 Mar 2020 13:31:05 GMT

Hi,

I want to remove duplicates within same by group which have higher value of time difference variable.

The current data:

Obs ID date1 time1 date2 time2 TimeDifference

1 1 03/08/20 11:00 03/08/20 10:55 5

2 1 03/08/20 11:00 03/08/20 10:15 45

3 1 03/08/20 11:00 03/08/20 9:30 90

i want to delete last two observations. I want to do this for multiple IDs with multiple such rows.

Thanks.

How to print out the observations from the set variables

CynthiaWei — Sun, 08 Mar 2020 19:04:18 GMT

Hi SAS Pros,

I have a data with a set of variables: drug1, drug2, drug3, ... until drug80. I am listing some of them right now.

Have:

drug1	drug2	drug3	drug4	drug5	drug6	drug7	drug8
a	c	e	i	b	d	h	n
b	c	e	g	h
a	b	d	h	j	k	l	o
b	d	h	j	k
h	i	j	k
u	v
x	w	y	z

Want: I want to print out all the drugs that in the above, like a drug list.

drug_list

Thank you in advance for any help!

Best regards,

Find Observations where string starts with specific Characters

mlogan — Sat, 07 Mar 2020 15:37:13 GMT

Hi,

I want to get all the observations where first name starts with Ro, Ay, Su OR Last name starts with Che, Ro.

I know it's possible to code with Where, IF etc, but can someone help with the coding with Perl, please. Thanks.

data have;
infile datalines;
input id First_name$8. Last_name&$8.;
datalines;
101 Roy     Rose
102 Yao 	Chen
103 Sushan  Hash
104 Robin   Blue
105 Susan   Robert
106 Susan   Jane
107 Ayesha  Hasan
;
run;

How to use ENC_keys in amazon redshift bulk loading in SAS 9.4 M6

ganesh56271 — Thu, 05 Mar 2020 16:17:02 GMT

HI Team,

we are trying to encrypt our amazon s3 bucket ACCESS ID & SECRET Keys. so i had followed below process but still getting no access key ID found error.

Steps followed:

1. created ENCKEY ADD statment (Xname)

2. Now trying to use the same name but its not recognizing ENCKEY name and giving ACCESS KEY ID not found error.

ERROR: Message from TKS3: No access key ID specified.

NOTE: The DATA step has been abnormally terminated.

List Hadoop Tables inside SAS

jimbarbour — Thu, 05 Mar 2020 21:39:58 GMT

I'm running SAS 9.4 M6 on a 64 bit Windows Server 2016 machine. I have both Enterprise Guide (8.1) and Display Manager available to me, but I typically use EG.

I'm connecting to a Hadoop database. I can create a Libname, or I can access the database via PROC SQL and explicit pass through. Both methods are using ODBC.

ISSUE: I can't get a list of Hadoop tables into my SAS session.

If I know the table name in advance, I can successfully run a query, but it's darned inconvenient not to be able to get a list of tables.

In past shops, I've been able to see a list of the Hadoop tables after assigning a Libname in the SAS EG "Servers" panel. In this case, I can see the Libname, but when I click on the drop down, there are no tables. Display Manager behaves similarly. See SAS code for Libname below.

I tried running a Proc SQL against DICTIONARY.TABLES, but I got no rows in return.

I also tried using PROC DATASETS. In my log, I get:

29         PROC	DATASETS
30         	LIBRARY=HIVEDB
31         	;
ODBC: AUTOCOMMIT is YES for connection 1
ODBC: Called SQLTables with schema of NULL
WARNING: No matching members in directory.
32         RUN;

I can do a SHOW TABLES via an explicit pass through, but nothing gets returned to the SAS log.

Question 1: I notice in my log from my Proc Datasets the message, "Called SQLTables with schema of NULL". Is there a way to tell Proc Datasets that I want a particular schema? I did specify a schema on my Libname, but apparently it's not coded correctly or isn't getting passed to Proc Datasets. Here's my Libname:

libname hiveDB odbc noprompt="uid=jbarbour; pwd=XXXXXXXXX; dsn=OPSI_HIVE_STG1; 
host=dbms0502; port=10000;schema=edps; authmech=3";

Question 2: Is there a way I can redirect the output of SHOW TABLES to the SAS log? I have the following options in effect:

options SASTRACE=",,,ds" sastraceloc=saslog nostsuffix;
options source source2 mprint fullstimer notes fmterr;
options msglevel=i;

Thank you in advance,

Jim

update target table with another source table using partial variables

CHL0320 — Thu, 05 Mar 2020 16:43:07 GMT

Hi,

I would like to learn how to update a target table with values from another table. There are about 1000 variables in the target table and only about 350 variables (chat or num variables) need to be updated from a renew dataset. The rules are

If the renew dataset has value in the variables then use that value in the target dataset.
If the renew dataset has missing value then keep the value in the target dataset.
If both target and renew datasets have missing value then keep the value as missing.

I have generated a very simple example.

data target;
input id1$ id2 a b c$ d$ e f;
datalines;
1 10 6 8 x1 x2 9 3
2 20 5 . y1 . 6 .
;
run;

data renew;
input id1$ id2 a b c$ d$ e f;
datalines;
1 . 6 8 x10 . 9 30
2 20 . 3 . y20 6 .
;
run;

the data want will be

id1	id2	a	b	c	d	e	f
1	10	6	8	x10	x2	9	30
2	20	5	3	y1	y20	6	.

Thank you so much.