jcis7 Tracker

Re: one to many update

jcis7 — Tue, 16 Jul 2024 22:47:52 GMT

Thanks for pointing out I forgot to make variableX character.

I need to update variableX only for years 2020-21 through 2023-24 so wondering how I do that. Thanks.

one to many update

jcis7 — Tue, 16 Jul 2024 22:07:35 GMT

G'day.

Appreciate any help you can give.

I've a file named fileA that has a unique identifying variable facility_code, no duplicates and it has variableX that I need to populate in fileB for certain years.

I've a second file named fileB (combined years of data) with the facility_code listed once for each year so since the file which has 7 years worth of data, if the facility reported information each year, it will be listed 7 times. It may not have reported each of the years, only some.

fileB is missing data for variable X in years 2020-21 through 2023-24.

I have a variable in fileA named variableX I want to populate in fileB for only 4 of the seven school years (2020-21, 2021-22, 2022-23, 2023-24).

How do i update fileB, specifically variable X only for the four school years with the information in FileA matched on facility_code for variable X only for specific school years?

I only know how to update the file if it is a one to one relationship in each file, for example if the facilty_code is listed only once in fileZ, code below, I know how to update it with the information in fileA (2nd SAS code window below).

thanks!

DATA fileA;
 INPUT facility_code $ facility_type$ physical_zip$ variableX schoolyr$;
CARDS;
1234567 public 14524 Yes 2019-20
1234568 public 23561 No 2019-20
1234569 public 84972 No 2019-20
0234567 public 94801 Yes 2019-20
0234568 public 89024 No 2019-20 
0234569 public 28201 No 2019-20

;
RUN; 


DATA fileB;
 INPUT facility_code $ status$ facility_type$ physical_zip$ variableX schoolyr$;
CARDS;
1234567 active public 14524 Yes 2017-18
1234567 closed public 14524 Yes 2018-19
1234567 active public 14524 No  2017-18
1234567 closed public 14524 . 2021-22
1234568 active public 23561 Yes 2017-18
1234568 active public 23561 Yes 2018-19
1234568 active public 23561 Yes 2019-20
1234568 active public 23561 . 2020-21
1234568 active public 23561 . 2021-22
1234568 active public 23561 . 2022-23
1234568 active public 23561 . 2023-24
1234568 active public 23561 . 2018-19
1234569 closed public 84972 . 2020-21
1234569 closed public 84972 . 2021-22
1234569 closed public 84972 . 2022-23
1234569 closed public 84972 . 2023-24
0234567 active public 94801 . 2021-22
0234568 active public 89024 . 2022-23
0234569 active public 28201 . 2023-24

;
RUN; 



DATA want;
 INPUT facility_code $ status$ facility_type$ physical_zip$ variableX $ schoolyr$;
CARDS;
1234567 active public 14524 Yes 2017-18
1234567 closed public 14524 Yes 2018-19
1234567 active public 14524 No 2017-18
1234567 closed public 14524 Yes 2021-22
1234568 active public 23561 Yes 2017-18
1234568 active public 23561 Yes 2018-19
1234568 active public 23561 No 2019-20
1234568 active public 23561 No 2020-21
1234568 active public 23561 No 2021-22
1234568 active public 23561 No 2022-23
1234568 active public 23561 No 2023-24
1234568 active public 23561 No 2018-19
1234569 closed public 84972 No 2020-21
1234569 closed public 84972 No 2021-22
1234569 closed public 84972 No 2022-23
1234569 closed public 84972 No 2023-24
0234567 active public 94801 Yes 2021-22
0234568 active public 89024 No 2022-23
0234569 active public 28201 No 2023-24

;
RUN;

data transaction;
  set fileA (keep=facility_code variableX);
run;

*--sort transaction;
proc sort data=transaction;
  by facility_code;
run;

*--sort list to be updated;
proc sort data=fileZ;
  by facility_code;
run;

*-update;
data updatedZ;
   update fileZ transaction updatemode=missingcheck;   
   by facility_code;
run;

Re: Finding a cutoff value across years for a specific ID

jcis7 — Thu, 18 Aug 2022 21:18:30 GMT

Thank you! I looked up Sum Function and found: SAS Help Center: SUM Function<>
So now, this works: 😊
if sum (of lt90vax(*))=4 then any4=1;
else any4=0;
run;

Re: Finding a cutoff value across years for a specific ID

jcis7 — Thu, 18 Aug 2022 19:38:30 GMT

Great - thank you!

* The code worked until:

401
402 if sum of lt90vax(*))=4 then any4=1;
--
22
76
ERROR 22-322: Syntax error, expecting one of the following: (, [, {.

ERROR 76-322: Syntax error, statement will be ignored.

403 else any4=0;
404 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WANT may be incomplete. When this step was stopped there were 0 observations and 14 variables.
WARNING: Data set WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.00 seconds

* I tried adding a parenthesis and got the following error:

441 end;
442
443 if sum of (lt90vax(*))=4 then any4=1;
-- -
22 386
68 200
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ,
GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, ^=, |, ||, ~=.

ERROR 386-185: Expecting an arithmetic expression.

ERROR 68-185: The function OF is unknown, or cannot be accessed.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

444 else any4=0;
445 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set N.WANT may be incomplete. When this step was stopped there were 0 observations and 14 variables.
WARNING: Data set N.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.01 seconds

* Then I tried removing the 'of' and got the following error:

461 end;
462
463 if sum (lt90vax(*))=4 then any4=1;
-
386
200
76
ERROR 386-185: Expecting an arithmetic expression.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 76-322: Syntax error, statement will be ignored.

464 else any4=0;
465 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set N.WANT may be incomplete. When this step was stopped there were 0 observations and 14 variables.
WARNING: Data set N.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.43 seconds
cpu time 0.03 seconds

Thank you!

Re: Finding a cutoff value across years for a specific ID

jcis7 — Thu, 18 Aug 2022 16:55:21 GMT


proc transpose data=vax out=vax_wide prefix=pctvax;
by id;
id year;
var pctvax;
run;

data vax_wide1;
  set vax_wide;
 
  if pctvax2000 < 90 then pctvax2000LT90=1;
  if pctvax2001 < 90 then pctvax2001LT90=1;
  if pctvax2002 < 90 then pctvax2002LT90=1;
  if pctvax2003 < 90 then pctvax2003LT90=1;
  if pctvax2004 < 90 then pctvax2004LT90=1;
  if pctvax2005 < 90 then pctvax2005LT90=1;

if sum (pctvax2000LT90, pctvax2001LT90, pctvax2002LT90, pctvax2003LT90, pctvax2004LT90, pctvax2005LT90) =4 then any4=1;
  else any4=0;
run;

This seems code above seems work for the simplified dataset I created. I'm running into a problem where in the actual dataset, it counts missing values as <90. I can post it as a separate post. Thank you everyone for your help!

Re: Finding a cutoff value across years for a specific ID

jcis7 — Thu, 18 Aug 2022 16:28:30 GMT

Thanks - I'm looking for school year which is in the results of the code you posted. I'm looking for the school year across the top with the school code listed in the column where the current school year is listed in the results of the code you posted.

Re: Finding a cutoff value across years for a specific ID

jcis7 — Thu, 18 Aug 2022 14:24:30 GMT

There are 6 years in the data (2000, 2001, 2002, 2003, 2004, 2005) and looking for IDs where pctvax<90 in any four years, for example:
Looking for ids with pctvax<90 in the following combination of years
2000, 2001, 2002, 2003
2000, 2002, 2003, 2004
2000, 2001, 2004, 2005
2001, 2002, 2003, 2004
Etc....

Thank you!

Finding a cutoff value across years for a specific ID

jcis7 — Wed, 17 Aug 2022 20:17:41 GMT

G'day,

I've a question -- I'm trying to find observations below a certain cutoff value across any four years for the same ID.

Here's the data below and what I tried. I realized it doesn't work. what i want is to have identify in a list that has ids which are below 90 for any four years (Yr), something like the table below. Any help you can give is much appreciated. Or is there a better way to do it in proc sql? Can this be done in the DATA step? Thank you!

id	pctvax2000	pctvax2001	pctvax2002	pctvax2003	pctvax2004	pctvax2005	pctLT90_2000	pctLT90_2001	pctLT90_2002	pctLT90_2003	pctLT90_2004	pctLT90_2005	any4
1234567	88	90	57	95	77	62	1	0	1	0	1	1	4
1234568	82	91	53	78	90	95	1	0	1	1	0	0	5


*--2017-18 ;
DATA vax;
 INPUT id $ pctvax  yr $;
CARDS;
1234567 88 2000
1234567 90 2001 
1234565 57 2002
1234567 95 2003 
1234567 77 2004
1234567 62 2005 
1234568 82 2000
1234568 91 2001 
1234568 53 2002
1234568 78 2003 
1234568 90 2004
1234568 45 2005 

;
RUN; 

data vax1;
  set vax;
   if yr='2000' then do;
     pctvax2000=pctvax;
     if pctvax2000<90 then pctLT90_2000=1; 
   end;

   if yr='2001' then do;
      pctvax2001=pctvax;
     if pctvax2001<90 then pctLT90_2001=1; 
   end;
   
    if yr='2002' then do;
    pctvax2002=pctvax;
     if pctvax2002<90 then pctLT90_2002=1;      
   end;

      if yr='2003' then do;
     pctvax2003=pctvax;
     if pctvax2003<90 then pctLT90_2003=1; 
   end;

      if yr='2004' then do;
    pctvax2004=pctvax;
     if pctvax2004<90 then pctLT90_2004=1; 
   end;

      if yr='2005' then do;
     pctvax2005=pctvax;
     if pctvax2005<90 then pctLT90_2005=1; 
   end;

   if sum(pctLT90_2000, pctLT90_2001, pctLT90_2002, pctLT90_2003, pctLT90_2004, pctLT90_2005)=4 then  any4=1;
   else any4=0;

   run;

Re: proc report across usage

jcis7 — Mon, 02 May 2022 21:58:10 GMT

Hi Cynthia,

Appreciate your guidance.

I see the compute block needs to be different from what I have for pctutd for the ACROSS usage.

I tried using the following (not sure which column SAS sees utd as since I don't have it listed in the column statement

compute pctutd;
pctutd=c5.sum/c4.sum;
endcompute;

and I'm now I"m getting the following error message:

ERROR: The variable type of C5.SUM is invalid in this context.
ERROR: The variable type of C4.SUM is invalid in this context.

proc report across usage

jcis7 — Fri, 29 Apr 2022 21:04:36 GMT

G'day,

I have the following program: Part 1 & 2 runs without SAS errors except Part 3 doesn't run. I'm trying to add pctud so that is shows under type (i.e, ask SAS to calculate pctutd across the two different types) and also ask SAS to calculate it for the combined types (which is equivalent to pctutd in Part 2) so it will have 3 columns with pctutd: the first column (after locale, year, grade, and children) with pctutd that sums utd for all types (type=class & nonclass) and divides it by the totalchildren for all types, the 2nd column that sums utd for observations with type=class and divides it by totalchildren for type=class and the 3rd column with pctutd for nonclass, all grouped by locale and year. Any help you can give will be greatly appreciated. And it will have a last column that has the % of entities that have nonclass type: so sas counts and sums the number of entities that have nonclass type for example in locale North and divides it by the total of entities in locale North, etc... Thank you.

*Part 1--dataset;
data have;
   input locale $ year $ grade $ totalchildren  utd  type $;
   datalines;
North 2020 5th 120 119 class 
South 2020 5th 140 120 nonclass
East 2020 5th 18 10	nonclass
West 2020 5th 9 9 class
North 2020 5th 11 9 class


;


*Part 2--program that has no errors except it doesn't give output I want;

proc report data = have out=want nowd missing completerows ; 
column locale year grade totalchildren utd pctutd type; 

	define locale / group 'Locale'; 
	define year / group; define grade / group; 
	define totalchildren / analysis sum; 
	define utd / analysis noprint; 
	define pctutd / computed; 
		compute pctutd; 
		pctutd=utd.sum/totalchildren.sum; 
		endcompute;  
   define type / across 'Type'; 

   *--summarizes ALL; 
   rbreak before /summarize;    
   compute before; 
   locale= 'ALL'; 
   year="current"; 
   grade="5th"; 
   endcomp; 

run;

Current output

					Type
Locale	year	grade	totalchildren	pctutd	class	nonclass
ALL	current	5th	298	0.8959732	3	2
East	2020	5th	18	0.5555556	0	1
North	2020	5th	131	0.9770992	2	0
South	2020	5th	140	0.8571429	0	1
West	2020	5th	9	1	1	0


*--part 3 - nonworking program
proc report data = have out=want nowd missing completerows ; 
column locale year grade totalchildren type,pctutd
('Overall' type pctutd);

	define locale / group 'Locale'; 
	define year / group; define grade / group; 
	define totalchildren / analysis sum; 
	define utd / analysis noprint;  
	define pctutd / computed; 
		compute pctutd; 
		pctutd=utd.sum/totalchildren.sum; 
		endcompute;  
   define type / across 'Type'; 

   *--summarizes ALL; 
   rbreak before /summarize;    
   compute before; 
   locale= 'ALL'; 
   year="current"; 
   grade="5th"; 
   endcomp; 

run;

Re: Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 25 Apr 2022 22:30:34 GMT

Thank you everyone!

Re: Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 18 Apr 2022 23:03:45 GMT

Thanks for catching that I didn't name the dataset in the set statement in the 'data updateddataset' statement 'toupdatedataset' and instead named it 'toupdate'.

Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 18 Apr 2022 22:28:27 GMT

G'day!

Appreciate any help you can give.

I've two datasets with the following names and content:

'idsdataset' that has the variable name 'id' and 'type'
'toupdatedataset' that has the variable names 'id', 'type', and 'status'

I'd like to update the field 'status' in the dataset 'toupdatedataset' with the value 'N' for all of the observations in the 'idsdataset' dataset based on 'id' in both datasets.

The outcome I'm looking for is listed in the dataset 'updateddataset'.

How can I update 'status' field without needing to enter each 'id' individually? Thank you.


data idsdataset;
   input id $ type$;
   datalines;
1234567 Pub
1245678 Pub
;
data toupdatedataset;
   input id $ type $ status $;
   datalines;
1234567 Pub .
1245678  Pub . 
2349878 Pri N
3948573 Priv Y
;

data updateddataset;
  set toupdate;
if id in ('1234567' , '1245678') then status='N';
run;

Re: ERROR: At least one file associated with fileref IMPORT is still in use. ERROR: Error in the FIL

jcis7 — Fri, 08 Apr 2022 17:03:15 GMT

Thank you both🙂

Re: ERROR: At least one file associated with fileref IMPORT is still in use. ERROR: Error in the FIL

jcis7 — Thu, 07 Apr 2022 19:46:46 GMT

Can you help me understand: When you ask, am I trying to do something with the file that is active in the editor, do as you asking if I am trying to use a file that was created in the sas program that the filename statement is at the end of? Or are you asking if I'm trying to do something with the SAS program file that is in the filename statement?

Based on your question, I selected the 'Break' button to cancel anything that had been running. Then I reran the program that the filename statement is at the end of, and no error came up.

Then, I closed SAS, reopened it and the program ran with no errors including the filename import statement at the end. I am trying to figure out if I'm doing something wrong and not do it again in the future. Thank you.

ERROR: At least one file associated with fileref IMPORT is still in use. ERROR: Error in the FILENAM

jcis7 — Thu, 07 Apr 2022 18:41:33 GMT

G'day.

I have placed a filename near the end of a SAS program and am getting the following error message:

ERROR: At least one file associated with fileref IMPORT is still in use.
ERROR: Error in the FILENAME statement.

This is the filename code. When I run the file SAS program.sas by itself, I don't get an error statement. Howeber, when I run the program the code below sits in, I get the error statement.

filename importv 'C:\SAS Programs\SAS program.sas';
%include importv;

Any help you can give will e much appreciated. Thank you.

Re: NOTE: Data file J.ALL.DATA is in a format that is native to another host, or the file encoding

jcis7 — Fri, 04 Dec 2020 20:07:07 GMT

Thanks. I tried it and I am still getting the same encoding error.
When I check the dataset the encoding is the following: shift-jis Japanese (SJIS)

Re: NOTE: Data file J.ALL.DATA is in a format that is native to another host, or the file encoding

jcis7 — Fri, 04 Dec 2020 18:58:07 GMT

Hi, Thanks! I found the following https://support.sas.com/kb/51/586.html<>
and changed it but it asked me if I wanted to save the file and the only option is to save it as .txt or under All Files but not a cfg file. What next?

NOTE: Data file J.ALL.DATA is in a format that is native to another host, or the file encoding

jcis7 — Fri, 04 Dec 2020 17:45:14 GMT

G'day!

I just switched computers. I am having trouble with SAS as I had an already existing SAS file that I am asking SAS to recognize and am getting the following note and error:

NOTE: Data file J.ALL.DATA is in a format that is native to another host, or the file encoding does not match the session

encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce

performance

ERROR: Some character data was lost during transcoding in the dataset J.ALL.DATA. Either the data contains characters that are

not representable in the new encoding or truncation occurred during transcoding.

Any help you can give will be much appreciated. Thank you.

Re: Selecting within range

jcis7 — Mon, 15 Jun 2020 23:44:12 GMT

Edited and noted for future reference. I thought the 'insert SAS code' window would only show up as a small window on the post with a limited number of lines (like the message you create does). Apologies.