topic Re: How to declare a record in SAS in SAS Programming

How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 01:58:43 GMT

Hi Every one,

I am trying to convert my postgre sql code to SAS where I declared a record. What is the best way that I can declare a record in sas. How to implement the below functionality in sas either using Data step or proc sql. Any help is highly appreciated.

Below is my postgre sql code. It will declare a record and then iterate through each record at a time in the for loop. It uses the execute format to dynamically populate the values using all and year.

DO 
$do$
DECLARE
rec RECORD;
BEGIN
/* MAKE test1 TABLE */
		/* MAKE TABLE */
		CREATE TABLE test1 (Name varchar(15),year varchar(4),PRIMARY KEY (Name,year));
		/* POPULATE test1 WITH APPROPRIATE Name */
		INSERT INTO test1 (Name,year) SELECT b.Name, c.year FROM (SELECT * FROM test2) b, (SELECT DISTINCT year FROM test3) c;
		/* POPULATE test1 WITH 'ALL' ROWS */
		FOR rec IN SELECT DISTINCT year FROM test3 LOOP
			EXECUTE format('INSERT INTO test1 (Name,year) VALUES ($1,$2)')
				USING 'ALL', rec.year;
		END LOOP;
END
$do$;

Re: How to declare a record in SAS

Tom — Fri, 29 Nov 2019 01:37:45 GMT

What does "declare a record" even mean? Can you explain what that code is doing?

Looks a lot like you are trying to do this:

data test1;
  length Name $15 year $4 value 8 ;
  set test2 (keep=break year rename=(break=name))
      test3 (in=in3 keep=year)
  ;
  if in3 then break='ALL';
run;

But it is not clear why you created a variable name VALUE if you didn't plan to give it any values.

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 01:45:27 GMT

Hi Tom,

Based on your query, I edited my post. Please let me know if that is clear.

Thanks,

Ravi.

Re: How to declare a record in SAS

Tom — Fri, 29 Nov 2019 01:49:25 GMT

@rkarr5 wrote:

Hi Tom,

Based on your query, I edited my post. Please let me know if that is clear.

Thanks,

Ravi.

So it looks like I was right.

Why did you create the variable VALUE if you aren't going to give it any values.

Only thing I didn't include was adding an index (why would you need that?) or selecting distinct (why does your source data have duplicates)?

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 01:56:08 GMT

I removed the value. I had a query with multiple loops and that value is used for the next loop. But I don't want to make it complex so I removed the value variable. Coming to the second part that you asked, there will be definitely duplicated values of years in my table. That's the reason I used the distinct. If you ask why do you have duplicate then I can't answer that as my data structure is like that which has duplicate records of year. For example a student who joined in multiple courses in the same year will have multiple records with same year.

Re: How to declare a record in SAS

Reeza — Fri, 29 Nov 2019 02:10:34 GMT

Don’t do conversion line by line. Instead figure out what a specific process is doing and explain it with a data example, showing the input and output and explain the logic. This way, we can help point you to the best SAS solution. By groups may make the the need for loops unnecessary for starters.

@rkarr5 wrote:

I removed the value. I had a query with multiple loops and that value is used for the next loop. But I don't want to make it complex so I removed the value variable. Coming to the second part that you asked, there will be definitely duplicated values of years in my table. That's the reason I used the distinct. If you ask why do you have duplicate then I can't answer that as my data structure is like that which has duplicate records of year. For example a student who joined in multiple courses in the same year will have multiple records with same year.

Re: How to declare a record in SAS

Tom — Fri, 29 Nov 2019 02:10:47 GMT

What is the purpose of TEST3? Wouldn't it make more sense to get the list of ALL years from test2?

select distinct break as name,year from test2
union
select distinct "ALL" as name,year from test2

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 19:42:53 GMT

StudentIdNo	Region	Age	agebrk	region_desc	subregion_desc	demo_age	gender	race	year
1	1A	12	12 to 17	Central	Chicago	age12to17	male	white_rac	2013
2	1B	14	12 to 17	Northern	Aurora	age12to17	female	white_rac	2014
3	1C	7	6 to 11	Southern	Dallas	age6to11	male	hispanic	2015
4	1D	3	Under 3	Central	Chicago	age0to2	female	white_rac	2016
5	1D	5	3 to 5	Northern	Aurora	age3to5	female	white_rac	2017

So this is my data. The code I pasted is trying to break this data into different segments based on age,gender, race,year. So there will be a different combinations for different variables. The last for loop is trying to create a ALL value with different years. So the last for loop should append ALL and 2012, ALL,2013,ALL,2014....so on....

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 19:45:38 GMT

Hey Tom,

The issue here is data step will update the value to All when ever it encounters the test3 dataset, But my requirement is after it gets all the data from both the datasets, it should append all value and the year for distinct years in test3 dataset.

Re: How to declare a record in SAS

Reeza — Fri, 29 Nov 2019 19:53:57 GMT

So you need this to dynamically filter the data set?
Or are you trying to summarize/count it?

In SAS this would be good to switch to a macro or PROC MEANS which can do multilevel summaries if that's what you want.

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 19:54:53 GMT

I want to dynamically filter the dataset.

Re: How to declare a record in SAS

Reeza — Fri, 29 Nov 2019 19:57:41 GMT

Then you likely want a macro, here are some resources on macros.

If you show what fields you want to control and how you want to pass the values it becomes very easy in SAS to do this, IMO. You switch your 'records' to macro variables basically and convert the filter to a SAS process.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 20:01:50 GMT

will look into that. Can you also let me know how to start converting the above code to macros. That would be very helpful.

Re: How to declare a record in SAS

Reeza — Fri, 29 Nov 2019 20:11:40 GMT

The links above have everything you need. What's your SAS coding level? A filter is a very simple procedure.

https://video.sas.com/detail/video/4573016761001/filtering-a-sas-table-in-a-data-step?autoStart=true&page=1&q=filter

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 20:18:38 GMT

Thanks for the video. I am a base programmer. But here my requirement is I need to create a table with two columns Name and year which gets it value from two different tables. I used proc sql to create the table with two variables and then inserted the data using insert into table_name(value,year) select b.value,c.year from(select * from test2)b,(select distinct year from test3)c. So that query is creating a table and inserting values into the value. Then I created another table which has distinct values of year and then set it to new dataset in data step and then assigned value to 'ALL' for all the distinct years. This serves my purpose but I want to know if there is any other alternative that will do it in single go.

Re: How to declare a record in SAS

Reeza — Fri, 29 Nov 2019 20:20:42 GMT

Unless you post what I initially request, sample input, example output and the logic we can't help you refactor your code.
Vague descriptions, get vague answers unfortunately.

Re: How to declare a record in SAS

Tom — Fri, 29 Nov 2019 20:20:57 GMT

Seems trivial to do in SAS. First lets convert your posted listing into an actual dataset.

data have;
  infile datalines dsd dlm='|' truncover;
  input StudentIdNo $ 
        Region $ 
        Age 
        agebrk :$10.
        region_desc :$20. 
        subregion_desc :$20.
        demo_age :$10. 
        gender :$7. 
        race :$20. 
        year 
  ;
datalines;
1|1A|12|12 to 17|Central|Chicago|age12to17|male|white_rac|2013
2|1B|14|12 to 17|Northern|Aurora|age12to17|female|white_rac|2014
3|1C|7|6 to 11|Southern|Dallas|age6to11|male|hispanic|2015
4|1D|3|Under 3|Central|Chicago|age0to2|female|white_rac|2016
5|1D|5|3 to 5|Northern|Aurora|age3to5|female|white_rac|2017
;

Then to get the break down by region and year just use PROC SUMMARY.

proc summary data=have ;
  class region year ;
  output out=want;
run;

proc print data=want;
run;

Obs    Region    year    _TYPE_    _FREQ_

  1                 .       0         5
  2              2013       1         1
  3              2014       1         1
  4              2015       1         1
  5              2016       1         1
  6              2017       1         1
  7      1A         .       2         1
  8      1B         .       2         1
  9      1C         .       2         1
 10      1D         .       2         2
 11      1A      2013       3         1
 12      1B      2014       3         1
 13      1C      2015       3         1
 14      1D      2016       3         1
 15      1D      2017       3         1

Look at the documentation of PROC SUMMARY (also known as PROC MEANS) to what else it can do for you.

Re: How to declare a record in SAS

Tom — Fri, 29 Nov 2019 20:42:01 GMT

@rkarr5 wrote:

Hey Tom,

The issue here is data step will update the value to All when ever it encounters the test3 dataset, But my requirement is after it gets all the data from both the datasets, it should append all value and the year for distinct years in test3 dataset.

Not sure why you would need to use "record" pointers to do that in your existing database. Doesn't it support normal basic SQL code?

create table want as 
  select distinct x,y from source1
;
insert into want 
  select distinc 'ALL' as X, Y from source2
;

I still think that it looks like your overall goal is to summarize data and PROC SUMMARY is a great tool to do that.

Re: How to declare a record in SAS

rkarr5 — Fri, 29 Nov 2019 20:49:08 GMT

Thanks Tom. Will check this code. I will try to explore proc summary also.