Need a macro to loop through SharePoint surveys and create datasets

[kmusa]

Hello,

I used this document to create a dataset from a SharePoint survey. (I am using SAS 9.4 on Windows 7 Professional 32-bit Operating System)

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Winnipeg-User-Group/HarryDroo...

I have 28 surveys that I need to create datasets from. Right now I have this code for each of the 28 surveys.

/* Start creating the datasets	*/
	%let dir = T:\FolderName\SubfolderName;

/* 1. Individual Application	*/

	%let xmlfile1 = ha_data1.xml;
	filename listout1 "&dir\&xmlfile1";

	proc http
	out = listout1
	url="&url?Cmd=Display&List=&ListID1&View=&ViewID1&XMLDATA=TRUE"
	method = "get"
	;
	run;

	filename HAabsent "&dir\&xmlfile1";
	filename sxlemap "&dir\HA1.map";
	libname HAabsent xmlv2 xmlmap=sxlemap
	access=readonly;

	/* Create the dataset */
	data sp_list_data01;
	set HAabsent.row ;
	run;
	quit;

I have created a table with the Form_number (Survey number), Form_name (Survey name), ListID, ViewID (for the surveys).

So this is how those variables fit into the code:

	/*1. the name of the xml data for the form*/
		%let xmlfile(Form_number) = ha_data(Form_number).xml;
		filename listout(Form_number) "&dir\&xmlfile(Form_number)";

	/*2. run the proc http for the form (issue the HTTP request to the web service)*/
		proc http
		out = listout(Form_number)
		url="&url?Cmd=Display&List=&(ListID)&View=&(ViewID)&XMLDATA=TRUE"
		method = "get"
		;
		run;

	/*3. use a previously created map to create an xml for each form*/
		filename HAabsent "&dir\&xmlfile(Form_number)";
		filename sxlemap "&dir\HA(Form_number.map";
		libname HAabsent xmlv2 xmlmap=sxlemap
		access=readonly;

	/*4. create the dataset*/
		data sp_list_data(Form_number);
		set HAabsent.row ;
		run;
		quit;

I'm sorry I can't provide test sharepoint survey data - it's on a secure site. Here is the macro I have so far but I realize it doesn't reference my table where the ListID and ViewID data resides:

%macro survey(num);
	%do i = 1 %to #
		proc http
		out = listout&i
		url="&url?Cmd=Display&List=&ListID&View=&ViewID&XMLDATA=TRUE"
		METHOD = "get"
		;
		run;
	%end;
%mend;

%survey(28);

Links, suggestions, even partial would be a huge help. I've learned so much from the people who have posted here, I'm so appreciative.

Thanks in advance.

Kathy

[ballardw · Accepted solution]

If you have all of the information in a data set (recommended) you could use the variables in the data set to build the strings to submit with call execute statements. The process is similar to creating a macro in determining which is boiler plate and which pieces need to change. Then building the strings to submit for the changing bits.

Suppose I had two parameters used to indicate part of dataset name and a variable and wanted to generate a bunch of (pretty trivial) data steps similar to

data outname(suffix)

   set start(suffix);

   rename <variable> = Constantname;

run;

if my data set has two variables: suffix and variable:

Data _null_;

   set mydata; /* has the control informat*/

   oname = cats('outname',suffix);

   inname= cats('start',suffix);

   call execute('data '||oname||';');

   call execute('set '||inname||';');

   call execute( 'Rename '||variable||'= Constantname; run;');

run;

Call execute stacks up code lines that execute AFTER the data _null_ set completes. It isn't required that a single Call Execute call is a complete instruction and notice that the run; statement was included as part of the line with Rename.

This will allow having all of the data step string manipulation functions to combine values and since URL seems to contain &instructions then placing them in single quotes for combining with other text seems like a way to head of some headaches.

[kmusa]

Thank you for your response. I think this may be the way to go. I'm having trouble creating my strings in my proc http statements.

I added a field "listout" to my table that has the resulting location and name of the desired XML file. I also added a field "url" to the table with the url of my SharePoint surveys.

data _null_;
	set Listid_viewid;
	call execute('proc http out = '||listout||'
				 url="'||url||'?Cmd=Display&List=&'||ListID||'&View=&'||ViewID||'&XMLDATA=TRUE"
				 METHOD = "GET"; RUN;');
run;

When I run this I get Error 22-322 Syntax error for each of the surveys

proc http out = T:\Folder\Subfolder\ha_data28.xml

-

22

76

ERROR 22-322: Syntax error, expecting one of the following: ;, AUTH_ANY, AUTH_BASIC, AUTH_NEGOTIATE, AUTH_NONE,

AUTH_NTLM, CLEAR_CACHE, CLEAR_CONN_CACHE, CLEAR_COOKIES, CT, EXPECT_100_CONTINUE, FOLLOWLOC, HEADERIN,

HEADEROUT, HEADEROUT_OVERWRITE, HTTP_TOKENAUTH, IN, METHOD, NOFOLLOW, NOFOLLOWLOC, NO_CONN_CACHE,

NO_COOKIES, OUT, PROXYHOST, PROXYPASSWORD, PROXYPORT, PROXYUSERNAME, PROXY_AUTH_BASIC,

PROXY_AUTH_NEGOTIATE, PROXY_AUTH_NONE, PROXY_AUTH_NTLM, URL, VERBOSE, WEBAUTHDOMAIN, WEBPASSWORD,

WEBUSERNAME.

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

[kmusa]

I finally got this to work using your suggestion. Here it is - thanks so much!

data _null_;
	set Listid_viewid;
	ha_data=cats('ha_data',Form_number,'.xml');
	HA=cats('HA',Form_number,'.map');
	sp_list_data=cats('sp_list_data',Form_number);
	call execute('%Let xmlfile = '||ha_data||';');
	call execute('filename listout "&dir\&xmlfile";');
	call execute('proc http out = listout
				 url="&url?Cmd=Display&List='||ListID||'&View='||ViewID||'&XMLDATA=TRUE"
				 METHOD = "GET"; RUN;');
	call execute('filename HAabsent "&dir\&xmlfile";');
	call execute('filename sxlemap "&dir\'||HA||'";');
	call execute('libname HAabsent xmlv2 xmlmap=sxlemap access=readonly;');
	call execute('data '||sp_list_data||';');
	call execute('set HAabsent.row;');
	call execute('run;');
	call execute('quit;');
run;
quit;

[ballardw]

---

@kmusa wrote:

I finally got this to work using your suggestion. Here it is - thanks so much!

data _null_;
	set Listid_viewid;
	ha_data=cats('ha_data',Form_number,'.xml');
	HA=cats('HA',Form_number,'.map');
	sp_list_data=cats('sp_list_data',Form_number);
	call execute('%Let xmlfile = '||ha_data||';');
	call execute('filename listout "&dir\&xmlfile";');
	call execute('proc http out = listout
				 url="&url?Cmd=Display&List='||ListID||'&View='||ViewID||'&XMLDATA=TRUE"
				 METHOD = "GET"; RUN;');
	call execute('filename HAabsent "&dir\&xmlfile";');
	call execute('filename sxlemap "&dir\'||HA||'";');
	call execute('libname HAabsent xmlv2 xmlmap=sxlemap access=readonly;');
	call execute('data '||sp_list_data||';');
	call execute('set HAabsent.row;');
	call execute('run;');
	call execute('quit;');
run;
quit;

---

Good!

I quite often have a long string variable that I can use the various the SAS string functions to build and then use

Call execute(string);

That way I can  use a Put statement to check the resolution of the functions before actually having the call execute statement execute.