renjithr Tracker

Re: How to create sub-totals ?

renjithr — Thu, 27 Jul 2017 19:32:42 GMT

Thank you!

Re: How to create sub-totals ?

renjithr — Wed, 26 Jul 2017 22:43:51 GMT

Sure.

Here is my new have:

Race	Group	Location	Date	Count
White	All	LA	Aug-16	1
White	All	ANA	Aug-16	2
Black	All	LA	Aug-16	2
Black	All	ANA	Aug-16	3
Black	65P	ANA	Aug-16	1

Desired output:

Race	Group	Location	Date	Count
White	All	LA	Aug-16	1
White	All	ANA	Aug-16	2
White	All	Region	Aug-16	3
Black	All	LA	Aug-16	2
Black	All	ANA	Aug-16	3
Black	All	Region	Aug-16	5
Black	65P	ANA	Aug-16	1
Black	65P	Region	1-Aug	1

Please let me know.

Re: How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 22:09:51 GMT

Thanks.

Just curious ..if the GROUP and DATE are not constant,then what will be the code change?

Re: How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 21:13:59 GMT

Group variables should be RACE+GROUP+DATE. GROUP and DATE are always constant for a single value of RACE.

Length of LOCATION IS 10.

How to create sub-totals ?

renjithr — Tue, 25 Jul 2017 20:58:21 GMT

Hi,

I have a dataset like below:

Race	Group	Location	Date	Count
White	All	LA	Aug2016	1
White	All	ANA	Aug2016	2
Black	All	LA	Aug2016	2
Black	All	ANA	Aug2016	3

I want the dataset to be like this:

Race	Group	Location	Date	Count
White	All	LA	Aug2016	1
White	All	ANA	Aug2016	2
White	All	Region	Aug2016	3
Black	All	LA	Aug2016	2
Black	All	ANA	Aug2016	3
Black	All	Region	Aug2016	5

Please share your thoughts.

Re: PROC SQL : How to exclude records based on the values of a column in another table?

renjithr — Fri, 30 Jun 2017 15:31:17 GMT

Thanks Reeza!

Re: PROC SQL : How to exclude records based on the values of a column in another table?

renjithr — Fri, 30 Jun 2017 15:31:00 GMT

Apologies!

Your solution works, Thank you so much !

Re: PROC SQL : How to exclude records based on the values of a column in another table?

renjithr — Thu, 29 Jun 2017 23:23:30 GMT

Hi ChrisNZ,

Sorry, I am afraid it is not the solution.

Re: PROC SQL : How to exclude records based on the values of a column in another table?

renjithr — Thu, 29 Jun 2017 23:00:28 GMT

Thanks Kiranv!

But you query is only returning 103. I need both 102 and 103.

PROC SQL : How to exclude records based on the values of a column in another table?

renjithr — Thu, 29 Jun 2017 22:50:42 GMT

I have a table that contains member id and then I wanted to join to another table using member id and look for the member spending code. A member can have multiple spending code, so what I want to achieve is to remove the member from the fianl result if the member has a spending code 'A1'.

Using PROC SQL , how can the below results be achieved?

Here is the example:

Member table:

Member_ID

101

102

103

Spending table

Member_ID Spending_code

101 B1

101 CC

101 A1

102 CC

102 DD

103 DD

My desired result is:

Member_id

102

103

Could you please help?

How to effectively manage version control on SAS EG(UNIX platform)?

renjithr — Tue, 13 Jun 2017 16:48:37 GMT

Hi Everyone,

In my department, we started using SAS EG on UNIX for all our production jobs and we are doing the version control manually and it takes a lots of time and are prone to errors

Just wondering if anyone has any recommendations for version controling on SAS EG(UNIX), using any free tools or using any feature in SAS EG itself.

Please let me know.

Thank you in advance.

Re: How to create a volatile table in oracle pass-thru SAS SQL code?

renjithr — Thu, 25 May 2017 22:00:57 GMT

Thank you, I am still getting an error.

ERROR: ORACLE execute error: ORA-00905: missing keyword.

How to create a volatile table in oracle pass-thru SAS SQL code?

renjithr — Thu, 25 May 2017 20:11:13 GMT

Hi All,

I am trying to figure out how I can create temporary tables/volatile tables in SAS - ORACLE pass-thru sql.

Below is my code, but it is throwing an error saying

ERROR: ORACLE execute error: ORA-00933: SQL command not properly ended.

PROC SQL;
	CONNECT TO ORACLE  (PATH="CS.WORLD" USER="&uid_db2." PASSWORD="&pwd_db2.");
	EXECUTE ( 
		CREATE GLOBAL TEMPORARY TABLE QUERY_FOR_INSTN_ENCTR  as 
		 select * from connection to oracle
			(
   SELECT t1.RGN_CD, 
          t1.ENCTR_SK, 
          t1.ENCTR_SRC_SYS_CD, 
          t1.CORR_MRN_PLS_NB, 
          t1.LOC_IK, 
          t1.CARE_SETNG_IK, 
          t1.PTNT_RSDNC_ZIP_IK AS PTNT_RSDNC_ZIP_IK1, 
          t1.ENCTR_STRT_TS, 
          t1.ENCTR_END_TS, 
          t1.DRG_IK, 
          t1.PTNT_RSDNC_ZIP_IK, 
          t1.PRNCPL_ICD_DIAG_IK, 
          t1.PRMY_ATND_PRVDR_IK, 
          t1.MDC_IK, 
          t1.ADMT_PRVDR_IK, 
          t1.PRNCPL_ICD_PROC_IK, 
          t1.PRNCPL_PROC_PRVDR_IK, 
          t1.PTNT_AGE_YR_NB, 
          t1.PTNT_BRTH_DT, 
          t1.PTNT_GNDR_CD, 
          t1.INPAT_LOS_DAY_CT, 
          t1.TTL_LOS_DAY_CT
      FROM IA_V.INSTN_ENCTR t1
      WHERE t1.RGN_CD = '08' AND t1.ENCTR_STRT_DT BETWEEN '1Jan2016:0:0:0'dt AND '31Mar2017:0:0:0'dt AND 
           t1.ENCTR_SRC_SYS_CD = 'C' AND t1.PTNT_AGE_YR_NB < 18
		   ) on commit preserve rows) by oracle;
	

	Create table STEP150 as
		SELECT * FROM CONNECTION TO ORACLE
			(
		Select *
			from QUERY_FOR_INSTN_ENCTR
			);
			disconnect from oracle;
QUIT;

I am not sure my code/syntax is correct. Please advice.

Thanks

Re: How to upload CSV files to a website through SAS?

renjithr — Tue, 09 May 2017 15:41:45 GMT

Thank you Chris!

How to upload CSV files to a website through SAS?

renjithr — Tue, 09 May 2017 15:15:42 GMT

Hi,

I am asked to create a process to upload files(.CSV) to a website and I am wondering if there is a way in SAS/SAS EG to do this thru code. Please let me know.

The file I plan to upload has 20 columns(name, age, race ..etc).

Re: How to conditionally check the difference between rows?

renjithr — Tue, 25 Apr 2017 19:29:48 GMT

Thank you all for your suggestions!

Re: How to conditionally check the difference between rows?

renjithr — Fri, 21 Apr 2017 01:53:14 GMT

Trying to figure out the difference in the number of shots white and blacks received in each state.

Re: How to conditionally check the difference between rows?

renjithr — Thu, 20 Apr 2017 23:12:35 GMT

Sure, here is a snapshot of the real data:

RACE	STATE	vaccinated_cnt	DATE
WHITE	CALIFORNIA	2	Sep2016
WHITE	ARIZONA	1	Sep2016
WHITE	TEXAS	50	Oct2016
BLACK	CALIFORNIA	1	Sep2016
BLACK	ARIZONA	5	Sep2016
BLACK	TEXAS	10	Oct2016

Re: How to conditionally check the difference between rows?

renjithr — Thu, 20 Apr 2017 22:51:53 GMT

Hi,

No, it is not going to be the same..there are more states and the numbers of white and black are different too.For some states there will be two black and one white or one black and 4 white or 2 black and 1 white..etc.

How to conditionally check the difference between rows?

renjithr — Thu, 20 Apr 2017 22:35:27 GMT

Hi,

Could you please let me know if there is any way to conditionally check the difference between rows?

For eg:

I have the below dataset(Have), where I need to calculate the shot differences between WHITE and BLACK(race) in corresponding states. i.e calculate the difference between WHITE and BLACK in California then Arizona..etc(dataset NEED below):

Dataset -Have

Race Shot_rcvd Area

WHITE 10 CALIFORNIA

WHITE 20 ARIZONA

BLACK 21 CALIFORNIA

BALCK 5 ARIZONA

Dataset Need

Race Shot_rcvd Area Diff_in_shots

WHITE 10 CALIFORNIA -11

WHITE 20 ARIZONA 15

BLACK 21 CALIFORNIA 11

BALCK 5 ARIZONA -15

Please let me know your suggestions.